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.
| Author |
Topic |
|
melf
Starting Member
8 Posts |
 Posted - 2006-03-06 : 03:46:28
|
| Hi ,I want to replace all values like '4 th','5 th' and so on...with '4th','5th'.How can do that? Can I use replace statement?Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 04:02:55
|
yes. replace('4 th', ' ', '')----------------------------------'KH' |
 |
|
|
melf
Starting Member
8 Posts |
Posted - 2006-03-06 : 04:11:32
|
| Hi,Thanks for your immediate reply khtan. I think I did not state my problem properly, I will try to do it againThe string I am trying to replace will be'SILVERSTEIN BUILDING 5 th Floor........' etchere it can be anything like 5 th, 6 th , 100 th ,I need to remove the spaces after the number and before 'th'.I hope this helps you understand the issue.Regards,melf |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 04:16:27
|
| is it always the space after the number ? If not, try to post more sample data.----------------------------------'KH' |
|
|
|
melf
Starting Member
8 Posts |
Posted - 2006-03-06 : 04:24:40
|
| Yup, its always like that. It is a address column where we may have data similar to what I had given in my previous post. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 04:26:17
|
if there might be more than one spacing than use the 2nd methodselect stuff(data, patindex('%[0123456789]%', data) + 1, 1, '') as [method 1], left(data, patindex('%[0123456789]%', data)) + ltrim(right(data, len(data) - patindex('%[0123456789]%', data))) as [method 2]from( select 'SILVERSTEIN BUILDING 5 th Floor........' as data union all select 'SILVERSTEIN BUILDING 4 th Floor........' as data) d----------------------------------'KH' |
|
|
|
melf
Starting Member
8 Posts |
Posted - 2006-03-06 : 05:13:48
|
| Thanks, but the solution wont work if the number has more than one digit like 12 th or 129 th etc. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 05:36:05
|
Try this :select left(data, charindex(' ', data, patindex('%[0123456789]%', data)) - 1) + ltrim(right(data, len(data) - charindex(' ', data, patindex('%[0123456789]%', data)) - 1)) from( select 'SILVERSTEIN BUILDING 5 th Floor........' as data union all select 'SILVERSTEIN BUILDING 1243 th Floor........' as data) d----------------------------------'KH' |
|
|
|
|
|
|
|
|
Need help - Update statement