why the update is failing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

why the update is failing

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 07:58:20

select * from lp1 --only 1 colum LUM
--below is the data from column LUM



LUM
-------------------------------
AIX2 actuser1
+ actuser2
SOLARIS9 actuser4,actuser8


UPDATE LP1 SET lum = REPLACE(Lum,' ',',')

3 rows updated
select * from LP1

LUMP
-----------
AIX2 actuser1
+ actuser2
SOLARIS9 actuser4,actuser8

SELECT REPLACE('SOLARIS9 actuser4,actuser8',' ',',')
--SOLARIS9,actuser4,actuser8

Why the update is failing ? could anyone help

-Neil

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-06-26 : 08:04:33

What is the result of:


SELECT ASCII(SUBSTRING(LUM, 9, 1)) AS ASCIIValue
FROM lp1
WHERE LUM LIKE 'SOLARIS%'

If it is anything other than 32, then the separator is not a space.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 08:09:07

SELECT ASCII(SUBSTRING(LUM, 9, 1)) AS ASCIIValue
FROM lp1
WHERE LUM LIKE 'SOLARIS%

Result is 9

-Neil

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 08:24:42

quote:
Originally posted by aakcse

select * from lp1 --only 1 colum LUM
--below is the data from column LUM

LUM
-------------------------------
AIX2 actuser1
+ actuser2
SOLARIS9 actuser4,actuser8

UPDATE LP1 SET lum = REPLACE(Lum,' ',',')

3 rows updated
select * from LP1

LUMP
-----------
AIX2 actuser1
+ actuser2
SOLARIS9 actuser4,actuser8

SELECT REPLACE('SOLARIS9 actuser4,actuser8',' ',',')
--SOLARIS9,actuser4,actuser8

Why the update is failing ? could anyone help

-Neil

It looks like it is not generating any Errors!!

Try this please
UPDATE LP1 SET lum = REPLACE(Lum,char(32),char(44))

luck,

--------------------------
Get rich or die trying
--------------------------

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-26 : 08:37:16

There is a TAB instead of a SPACE hence the update can't work as you want.

set lum=replace(replace(lum,char(9),','),' ',',')

should fix it

No, you're never too old to Yak'n'Roll if you're too young to die.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 08:39:36

quote:
Originally posted by webfred

There is a TAB instead of a SPACE hence the update can't work as you want.

set lum=replace(replace(lum,char(9),','),' ',',')

should fix it

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes, this is a good catch :)

--------------------------
Get rich or die trying
--------------------------

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 08:47:21

Thanks Thats right when I pressed TAB instead of ' ' (Space) it worked.

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 08:51:13

How to identify if it is a tab or space? Tab also has spaces in it, does tab differs from other tab, as I have noticed some tabs have 1 space and other 2,3 etc.. are they all same?

-Neil

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 09:07:16

quote:
Originally posted by aakcse

How to identify if it is a tab or space? Tab also has spaces in it, does tab differs from other tab, as I have noticed some tabs have 1 space and other 2,3 etc.. are they all same?

-Neil

in MS SQL you can identify the the ASCII code for any character by executing:
SELECT ASCII(any character)
e.g
SELECT ASCII(' ')
result:
32.

white spaces are different from each other, but there's only one ascii code that represent either space, tab, new line...etc.

Luck,

--------------------------
Get rich or die trying
--------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources