How can I replace only "North" in this following string when it is by itself?
'E NORTHCREST DR NORTH'
UPDATE address SET streetname = REPLACE(streetname, ' NORTH',' N')
This script changes it to 'E NCREST DR N'
How can I replace only "North" in this following string when it is by itself?
'E NORTHCREST DR NORTH'
UPDATE address SET streetname = REPLACE(streetname, ' NORTH',' N')
This script changes it to 'E NCREST DR N'
Tricky one. I was able to use a combination of CASE, PATINDEX and REPLACE to solve this. Here's a sample script to demonstrate. It isn't pretty, but it works:
/* First, a test table to select from... */
create table #temp
(id int identity(1, 1) not null, myString varchar(100))
/* Now some test data... */
insert into #temp
(myString)
values
('E. NorthCrest Dr. North'),
('E. NorthCrest North Dr.'),
('E. NorthCrest Dr. South'),
('E. SouthCrest Dr. North'),
('E. SouthCrest Dr. West'),
('North SouthCrest Dr. West'),
('NorthCrest Dr. East')
/* And, the statement itself... */
select *,
case patindex ('% North %', myString)
when 0 then
case patindex ('% North', myString)
when 0 then
case patindex ('North %', myString)
when 0 then myString
else substring(myString, 0, patindex ('North %', myString))
end +
case patindex ('North %', myString)
when 0 then ''
else replace (substring(myString, patindex ('North %', myString), len(myString)), 'North', 'N')
end
else substring(myString, 0, patindex ('% North', myString))
end +
case patindex ('% North', myString)
when 0 then ''
else replace (substring(myString, patindex ('% North', myString), len(myString)), 'North', 'N')
end
else substring(myString, 0, patindex ('% North %', myString))
end +
case patindex ('% North %', myString)
when 0 then ''
else replace (substring(myString, patindex ('% North %', myString), len(myString)), 'North', 'N')
end as myString_CleanedUp
from #temp
-- Cleanup, and we're done!
drop table #temp
And, the results:
id myString myString_CleanedUp
1 E. NorthCrest Dr. North E. NorthCrest Dr. N
2 E. NorthCrest North Dr. E. NorthCrest N Dr.
3 E. NorthCrest Dr. South E. NorthCrest Dr. South
4 E. SouthCrest Dr. North E. SouthCrest Dr. N
5 E. SouthCrest Dr. West E. SouthCrest Dr. West
6 North SouthCrest Dr. West N SouthCrest Dr. West
7 NorthCrest Dr. East NorthCrest Dr. East
You can of course use a local variable in place of the literal to generalize the solution, but I'll leave that as an exercise for you.
I hope this gives you what you were looking for! Good luck!
@BitBlt: That's one sick query, which like all your queries will work.
Wouldn't it be easier and less time consuming to just:
select substring(REPLACE( ' ' + myString + ' ' , ' NORTH ',' N '),2,len(myString))
from #temp
Thank you guys!
@adam_k, that is very clever and MUCH simpler. I had to look at it a couple of times before I "got it". Well done!
However, it does leave an extra space on the end, so perhaps using LTRIM and RTRIM instead of SUBSTRING would be better?
Like so:
select rtrim(ltrim(REPLACE( ' ' + myString + ' ' , ' NORTH ',' N ')))
from #temp
But kudos to you for seeing the simpler solution!
Well, I believed that using the length of the original string as the length parameter for the substring it would remove the last space (ie 10chars the original string + 2 chars for spaces =12 substring(newstring,2,10) would remove the last space.
I failed to take into account that by replacing North with N the original string would be 4 (+2 for the spaces) chars longer that the new one.
You were right once again. ltrim(rtrim( is the way to go.
Thanks for correcting me.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.