Substring - Remove Characters

[Anddmx]

Hi,

I need some help retrieving some values from string.

I have tried few different ways but nothin seems to work.

Here is the string value (brand!sunshine)(r!58)(k!15228)
the only parts that are static is brand!, r!, k!, after that is the values I want to retrieve.

sunshine

58

15228

I could use substring but value length always changes and I know there is way to count lenght and I tried replace to remove "(" but then I have problem of getting the value.

Thanks

 

[mrdenny]

use PATINDEX to find the end of the string.

This nifty select will give you a good example.

[COLOR=blue]declare[/color] @string [COLOR=blue]varchar[/color](100)
[COLOR=blue]set[/color] @string = [COLOR=red]'(brand!sunshine)(r!58)(k!15228)'[/color]
[COLOR=blue]select[/color] [COLOR=#FF00FF]substring[/color](@string, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%brand!%'[/color], @String)+6, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%r!%'[/color], @string)-10),
	[COLOR=#FF00FF]substring[/color](@string, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%r!%'[/color], @string)+2, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%k!%'[/color], @string)-14-len([COLOR=#FF00FF]substring[/color](@string, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%brand!%'[/color], @String)+6, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%r!%'[/color], @string)-10))),
	[COLOR=#FF00FF]substring[/color](@string, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%k!%'[/color], @string)+2, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%)'[/color], [COLOR=#FF00FF]substring[/color](@string, [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%k!%'[/color], @string)+2, len(@string)))-1)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Anddmx]

Thanks you so much!

I was trying to use CHARINDEX....

 

[mrdenny]

Ah, yeah, that won't work for this application.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com