String manipulate

Question

String manipulate

Lital

SSC Eights!

Points: 901
More actions
September 26, 2005 at 1:13 am

#166090

Lo to all,
I hope someone can help.
my questions is:
i have a string " where strs in ('40301','40301.16') "
i need to take out only '40301' , '40301.16' from the string.
how can i do that?
thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up charindex and substring in Books online.

Basically, use Charindex to find the location of the quotes in the string, then substring to extract the portion you want.

DECLARE @STR VARCHAR(15)

SET @STR='ABC(XYZ)CBA'

SELECT SUBSTRING(@str,CHARINDEX('(',@str), LEN(@str)+1-(CHARINDEX(')',@str)-1))

hth

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

atra SSC Veteran Points: 256 More actions · Answer 2

declare @STR char(50)

set @STR = " where strs in ('40301','40301.16') "

select substring(@str,patindex('%(%',@str) + 1,patindex('%)%',@str) - patindex('%(%',@str) - 1)

Lital SSC Eights! Points: 901 More actions · Answer 3

its not working

declare

@STR char(50)

set

@STR = '' where strs in ('40301','40301.16') ''

select

substring(@STR,patindex('%(%',@STR) + 1,patindex('%)%',@STR) - patindex('%(%',@STR) - 1)

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'where'.

atra SSC Veteran Points: 256 More actions · Answer 4

try this one

declare @STR char(50)

select

@STR = " where strs in ('40301','40301.16') "

select

substring(@STR,patindex('%(%',@STR) + 1,patindex('%)%',@STR) - patindex('%(%',@STR) - 1)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The quotes are in the wrong place. Try this

set @STR = ' where strs in (''40301'',''40301.16'') '

The rest of atra's query should be fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SQL's not overly fond of double quotes. In fact, if quoted identifiers is set on (which is a default) your query returns the following error

Server: Msg 207, Level 16, State 3, Line 3

Invalid column name ' where strs in ('40301','40301.16') '.

Stick with single quotes and escape them as necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lital SSC Eights! Points: 901 More actions · Answer 7

declare @STR char(50)

select

@STR = " where strs in ('40301','40301.16') "

select

substring(@STR,patindex('%(%',@STR) + 1,patindex('%)%',@STR) - patindex('%(%',@STR) - 1)

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ''.

Lital SSC Eights! Points: 901 More actions · Answer 8

Lital

SSC Eights!

Points: 901

September 26, 2005 at 4:31 am

#592932

Thanks a lot its working