SQLTeam.com | Weblogs | Forums

String


#1

i have the follwoing code

UPDATE dbo.SSCLXWorkingDataloadFile3
SET SecurityName = RTrim(LTrim(
REPLACE(' ' + SecurityName + ' ', ' ' + @StringToFind + ' ', ' ' + @StringReplacement + ' ')

it works for the follwoing
stringtofind = s.a. Replace = SA

but not for
stringtofind = /the Replace =

any ideas why


#2

Its working fine when i executed the below code

select REPLACE('SecurityName /the SA','/the','')


#3

when i do it like this
UPDATE dbo.SSCLXWorkingDataloadFile3
SET SecurityName = RTrim(LTrim(
REPLACE(' ' + SecurityName + ' ', ' /Australia ', ' ')
))

say 1000 rows effected but nothing has changed


#4

got it to work to much space


#5

but there doesnt seem to be much spaces in the original code


#6

when i test my query like this
select REPLACE(REPLACE('Centrais Eletricas Brasileiras SA ',' AS ',' A/S '),'is','')

it removes the is from the text without adding A/S to anywhere were there is as in the text. but when i run my query it doesnt work.

this is the query
UPDATE dbo.SSCLXWorkingDataloadFile3
SET SecurityName = RTrim(LTrim(
REPLACE(REPLACE(' ' + SecurityName + ' ', ' '+ @StringToFind +' ', ' '+ @StringReplacement +' '), ''+@StringToFind +'', ''+@StringReplacement+'')
))
FROM dbo.SSCLXWorkingDataloadFile3
WHERE SecurityName IS NOT NULL

Is it because of the trims?


#7

after the replacement you are trimming the string. That would not be the case. Please check with the data in table. It may have special character or Enter, Tab like that


#8

any ideas why it wont work


#9

There is no space AS space to be changed.


#10

if i take out the space around the AS THEN Brasileiras BECOMES BrasileirA/S which i dont want. only want the AS TO CHANGE TO A/S when its not part of a word