String manipulations - 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

String manipulations

Author

Topic

webguy
Starting Member

3 Posts

Posted - 2006-09-13 : 18:47:08

Can someone point me in the right direction.

I have a string A - string string string ‡c string string string ‡h audio

I would like to move string ‡h audio to before the char ‡c and reword audio to sound

ie:

from this :
string string string ‡c string string string ‡h audio

to this :

string string string ‡h sound ‡c string string string

There are about 70 thousand records, not all contain the word audio, only about 5 thousand I think. So I must first single those out, then update them to the new format.

Any ideas?

thx!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-13 : 19:47:07

Based on your description, would it actually do this?

from this :
string string string ‡c string string string ‡h audio

to this :
string string string string ‡h sound ‡c string string

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-13 : 20:03:33

hope this will help
select Stuff(Replace(colname,'‡h audio',''),patindex('%‡c%',colname),2,'‡h sound ‡c') from tabname

Thanks,
Gopi Nath Muluka

webguy
Starting Member

3 Posts

Posted - 2006-09-14 : 11:24:17

quote:
Originally posted by snSQL

Based on your description, would it actually do this?

from this :
string string string ‡c string string string ‡h audio

to this :
string string string string ‡h sound ‡c string string

yes!

webguy
Starting Member

3 Posts

Posted - 2006-09-14 : 11:25:53

quote:
Originally posted by Gopi Nath Muluka

hope this will help
select Stuff(Replace(colname,'‡h audio',''),patindex('%‡c%',colname),2,'‡h sound ‡c') from tabname

Thanks,
Gopi Nath Muluka

Dude, if this works you're the man!

I'll go test it out.
I was looking through SQL server help file and found that STUFF function thinking it would be useful, guess it was! Thanks Much!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 11:45:23

quote:
Originally posted by Gopi Nath Muluka

hope this will help
select Stuff(Replace(colname,'‡h audio',''),patindex('%‡c%',colname),2,'‡h sound ‡c') from tabname

Thanks,
Gopi Nath Muluka

Watch out !!

This may not work for the cases where word "audio" is missing? Add a case condition to check for such cases

Harsh Athalye
India.
"Nothing is Impossible"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 12:59:01

You need to add a WHERE clause to find the 5000 you mentioned too, so it will be

SELECT ...
FROM tabname
WHERE colname like '%‡h audio'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources