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

SQL Server 2005 Forums

Transact-SQL (2005)

String Manipulation

Author

Topic

bi89405
Starting Member

35 Posts

Posted - 2010-08-19 : 10:25:39

Suppose I have two string fields returned in SQL. One that contains only one last name, call it String1. The other string contains a list of last names separated by a '/' one of which is String1, call this String2.

For example:

String1 = SMITH

String2 = SMITH/JONES/HARVEY

I would want my display string to be JONES/HARVEY.

The position of String1 can be in any area of String2. It can also displayed like this:

JONES/SMITH/HARVEY

and I need it to display JONES/HARVEY as well.

Any ideas how I can go about manipulating String2 to return a list of names minus String1?

Thanks in advance,
Zack H.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-19 : 10:33:55

One way..

select case when charindex(@String1 + '/' , @String2) > 0 
			then replace(@String2,@String1 + '/','')
			else replace(@String2,'/' + @String1,'')
		end

bi89405
Starting Member

35 Posts

Posted - 2010-08-19 : 10:39:34

Wow, nicely done. But what if string1 = string2. I want nothing to print or a empty string.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-19 : 10:53:18

[code]select case when charindex(@String1 + '/' , @String2) > 0
then replace(@String2,@String1 + '/','')
when @string1=@string2 then ''
else replace(@String2,'/' + @String1,'')
end
[/code]

Madhivanan

Failing to plan is Planning to fail

bi89405
Starting Member

35 Posts

Posted - 2010-08-19 : 11:13:20

Um, yea. I should've figured that out on my own. Nicely done! Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources