Hi !
I'm trying to put a space after . and , in varchar fields.
I've tried
UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')
but nothing happened
What's wrong ??
Thanks and sorry if my english isn´t so good.
Eduardo (from Argentina)
Hi all smart people,
Can someone please help me with how to replace null values with spaces in select statement.
I have to have a select statement which will select all the data but the field contain null value it has to replace with spaces. How can I do that ?
Thanks,
i have a field with blank spaces.
i wanna replace the spaces with just one spaces. ihave 500 fields in 500 tables.
any input will be appreacited.
i have something like this but its not working.
declare @field varchar(50)
declare @minVoter int
declare @maxVoter int
declare @tableName varchar(20)
set @tablename = '00001'
-- select ad_str1 from [00170]
select @minVoter = min(id_voter),
@maxVoter = max(id_voter) from quotename(@tablename)
while (@minVoter <= @maxVoter)
begin
select @field = ad_str1
from quotename(@tablename)
where id_voter = @MinVoter
update [00170]
set ad_str1 = replace(@field, ' ', ' ')
select @minVoter = min(id_voter)
from quotename(@tablename)
where id_voter > @minvoter
end
I need to map several columns of data from one database to anotherwhere the data contains multiple spaces (once occurance of a variablenumber or spaces) that I need to replace with a single space. Whatwould be the most efficient way to do this? I am using SQL2K. I wasthinking a function since I know of no single Transact-SQL commandthat can accomplish this task.
View 8 Replies View RelatedI have several fields that have multiple spaces between the City State and Zip Code. I want to be able to make only one space between each. A combination of Substring and Replace is what i have been trying but not able to make it work. a do while might be what i need but not sure how to do it.
View 10 Replies View RelatedHello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
I am trying to import a fixed width file where some of the numeric columns are empty. The columns in question are defined as integer columns (of varying sizes) and I am guessing that "empty" columns come across as multiple spaces on the import.
Even though I have "Retain null values from source" checked off, I am still receiving the following error on these empty columns:
Error: 0xC02020A1 at Input Data, Flat File Source [1]: Data conversion failed. The data conversion for column "ToContractExpiryYear" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Input Data, Flat File Source [1]: The "output column "ToContractExpiryYear" (51)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ToContractExpiryYear" (51)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Input Data, Flat File Source [1]: An error occurred while processing file "\Nastinus-01ClearingDataOCC20060320ser2mst.20060317" on data row 1.
If it is truly the system treating the column as spaces (and not trimming the value), then the only solution I can think off is to source everything as strings, perform a transform that executes a Trim() (Derived Column or Script ??), THEN perform a transform that converts data types, then do whatever else I need...
Am I missing something? Is this the correct solution?
I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?
I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.
When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.
So with this said my two questions:
How do I get the destniation column names wrapped in double quotes when using transformations?
or
Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?
Hello
In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:
UPDATE CAT_Products
SET Description = replace (Description, '</h3>', '</h2>')WHERE Description <> '</h2>'
UPDATE CAT_ProductsSET Description = replace (Description, '<h3>', '<h2>')WHERE Description <> '<h2>'
However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.
UPDATE CAT_ProductsSET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')WHERE Description <> '<h2>'
What do I need to adjust?
Thanks
Please how can remove spaces on a date field and a text field. THanks.
View 1 Replies View Relatedi collected the users information without using any trim fucntion(i have implemented now)
but the data which has been already posted into my server has text with some white spaces at the beginning of data
now how to remove this white spaces in this column in online server data.
the data is something like this
mycolumn
data
data
data
data
so how to remove white spaces in the above column
Hi
i have 1 query please guide me,
SELECT P.ProjectName, REPLACE(SPACE(TU.TaskOutlineLevel), ' ', '__') AS dash,TU.TASKISSUMMARY AS TASKSUMMARY,
here i want to add BLANKS but not working so i have to show add spaces using __ here any idea i can add SPACES or ' ' here.
please let me know if any. basically i want to align the data in my GRIDVIEW
Thanks in advance
Parth
How can you remove spaces in the middle of a string, RTRIM and LTRIM does not work
View 1 Replies View RelatedCreating a text file using DTS, is there a function/way to take out white
spaces from columns. Example:
'1234 ','567 ' would come in text as
'1234','567'
Thanks in advance.
Hi,
I am building my insert statements dynamically and am finding that there are spaces after certain integer fields.
example Insert Table A (col1, col2, col3)
values (1 ,'2',3 )
If col1 and col3 are integer fields, is there any affect to either how the value is stored or how it will be retrieved when the table is used in a join. By inspecting the values in the table, it seems fine. Do I need to worry?
Thanks,
Jim
If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!
Todd
--------------------------------------------------------------------------------
I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:
Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348
I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.
All help would be appreciated.
I need to trim what looks like two spaces from a field.
Example:
" 601274" needs to be "601274". Does anyone know the syntax?+
is there a way to do remove spaces from a string if its they are in deifferent places on each row
View 1 Replies View RelatedI'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.
Code Snippet
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL
--@debug bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM [scholarship]
WHERE ([sectionID] = @schoolID OR @schoolID IS NULL)
AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL )
AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL )
AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )
The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?
I have a single column value like this. I want to get the charindex value whereever it is a single space.
'6 REP5426 15936 022708 107/0000 33003301985 BAILEY AMY NICOL 11454 PP 25.00 000-00 00110080 A O '
Thanks.
Hi All... I'm using a SQL Server 2005 database. I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out. I guess I can understand why... But it's a pain dealing with it. As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet. What's the easiest/best way to get rid of those spaces. Geez, it'd be real cool if I could put something in the SELECT statement. Any thoughts? Thanks much!! -- Curt
What is the best way to check for leading spaces in your table, using ltrim?
Such as TableA(name, city)
the data in TableA Smith Dallas
John New York
Greg Richmond
David Chicago
Return only David.
Hey all,
Hope I can get some help here.
Have a view that has to reference a table in another database on the same server. This isn't the problem. I am just using Databasename..tablename in the from statement. Get the results required in testing.
The problem is that in production the table name has a space character. i.e. 'accounting info' would be the table name. I have not been able to figure out the proper syntax to capture this properly to reference in the from statement.
I have tried enclosing the name in ' ', " ", [], (), {} and just about every combination I can come up with.
Any help in this would be great, and changing the databse name isn't an option at this time.
Thanx,
Chris
I have some fields in my database that are char(50)
I use an insert to put some text into the fields (using perl)
when I look at the fields I find that spaces are used to "pad out" the text to the field size.
This didn't happen with MySQL and PHP, is there any way to disable this?
HI all,
I have a database that allocated:
Data: 7300 MB
Log: 2000 MB
But only used
Data: 5500MB
Log: 50MB
How can I free the unused space in the transaction log because
the database is getting too big.
Thank you for your suggestion.
i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here
(i'm an SQL guy, not an SQL Server guy)
how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')
select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string
i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string
is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?
Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:
UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')
so, i tried this query and it does nothing.
UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')
does anybody have any ideas? Thanks!
I know this is probably a DOS question but does anyone know how the following would work without taking out the spaces in the folder "Audit Tax Planning" (works fine in paths without spaces)?
Declare @ren varchar(255)
Declare @path varchar(100)
Set @path = '\mainWGDATAAccountingAudit Tax Planning'--G:AccountingAudit Tax Planning 5BE
IF EXISTS(SELECT id FROM tempdb..sysobjects WHERE id = object_ID('tempdb..#Tax_File'))
DROP TABLE #Tax_File
CREATE TABLE [#Tax_File](
[File_Name] varchar(50),
)
Set @ren = 'dir /B ' + @path + 'test.txt'
Insert into #Tax_File ([File_Name])
EXEC master..xp_cmdshell @ren
If not exists(select [File_Name]
from #Tax_File
where right([File_Name],4) = '.txt')
begin
RAISERROR ('File does not exist.', 16, 1)
Return
end
select * from #Tax_File
Hello,
I am fairly new to SQL I have started to administer a system which handles carrier information for a mail order system. The logic behind the system is quite simple there are 5 or so columns in a table the first column is the first part of the postcode i.e EX15, the other columns contain which delivery services and depot numbers are associated with that postcode. It works fine at the moment.
However now the main carrier has decided that they are now going to split these postcodes so for example EX15 1* goes to a different depot than EX15 2*
I cant seem to insert EX15 1 into the first column, I get the following error:
Attempt to store duplicate value in unique column. (-155)
Is this because of the space in 'EX15 1'? Because 'EX15' already exists in that column? In which case do I need to somehow tell SQL that there is a space there?
I hope this makes sense
Below is a snap of the table with the existing EX15 postcode data
postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15.....|NF/S93......|........1|34........|C.......|DEVO
EX15.....|NF/SAT......|........1|34........|C.......|DEVO
EX15.....|NF/930......|........1|34........|C.......|DEVO
EX15.....|NF/AM.......|........1|34........|C.......|DEVO
EX15.....|NF/ON.......|........1|34........|C.......|DEVO
EX15.....|NF/48.......|........1|34........|C.......|DEVO
EX15.....|NF/3D.......|........1|34........|C.......|DEVO
Here is a snap of how I would like to set up another entry:
postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15 1...|NF/S93......|........1|34........|C.......|DEVO
EX15 1...|NF/SAT......|........1|34........|C.......|DEVO
EX15 1...|NF/930......|........1|34........|C.......|DEVO
EX15 1...|NF/AM.......|........1|34........|C.......|DEVO
EX15 1...|NF/ON.......|........1|34........|C.......|DEVO
EX15 1...|NF/48.......|........1|34........|C.......|DEVO
EX15 1...|NF/3D.......|........1|34........|C.......|DEVO
Thanks
All- I'm using the SQL SP below to drive an asp.net gridview. Note how, after "else", I attempt to send to the browser the HTML code that should create a hard space. However, the characters are simply rendered verbatim by the browser. I've tried putting actual spaces in the quotes, and also the SPACE(x) argument. SP test executes appear to show the spaces in the results, but aps.net isn't rendering the spaces at all. Any thoughts.
(Its really more of an asp.net question, but I thought I'd post this here too for your thoughts. Thx in advance.)
SELECT person_id, male, female, last, CASE WHEN fam_adult = 1 then first else ' ' + first end as first, fam_adult, is_adult, is_kid, is_y_parent_or_kid, is_guest, is_person_type_5, grade, school, is_person_type_6, fam_dad
FROM person
In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?
View 6 Replies View Related