| Author |
 Topic  |
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
 Posted - 06/26/2012 : 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
Constraint Violating Yak Guru
476 Posts |
Posted - 06/26/2012 : 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
India
517 Posts |
Posted - 06/26/2012 : 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
USA
261 Posts |
Posted - 06/26/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/26/2012 : 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
USA
261 Posts |
Posted - 06/26/2012 : 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
India
517 Posts |
Posted - 06/26/2012 : 08:47:21
|
Thanks Thats right when I pressed TAB instead of ' ' (Space) it worked.
-Neil |
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 06/26/2012 : 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
USA
261 Posts |
Posted - 06/26/2012 : 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
-------------------------- |
|
|
| |
Topic |
|
|
|
why the update is failing
