get part of the string in between square brackets

Question

get part of the string in between square brackets

Nomvula

Hall of Fame

Points: 3778
More actions
June 5, 2013 at 6:29 am

#274753

hi
i have the following string '[N] 18. Is the C.B. removed from the panel? [Yes / No]' and i need to return only this part
18. Is the C.B. removed from the panel?
please help
thanks in advance

Viewing 7 posts - 1 through 7 (of 7 total)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

select LEFT('[N] 18. Is the C.B. removed from the panel? [Yes / No]',43)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

thanks for the quick response but it still returns [N] at the beginning whereas the results should be

18. Is the C.B. removed from the panel?

one other thing is that the length of the string will vary.

thanks again

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

One way to do this..

SELECTSUBSTRING

(

C.Col,

CHARINDEX(']',C.Col,1)+1,

LEN(C.Col) - CHARINDEX('[',REVERSE(C.Col),1) - CHARINDEX(']',C.Col,1)

)

FROM(

SELECT'[N] 18. Is the C.B. removed from the panel? [Yes / No]' AS Col UNION ALL

SELECT'[NABCD] 18. Is it removed from the panel? [Yes / No]'

) AS C

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Another couple of ways: -

-- IF FROM A TABLE

SELECT fin

FROM (VALUES('[N] 18. Is the C.B. removed from the panel? [Yes / No]')

)a([STRING])

CROSS APPLY (SELECT LTRIM(SUBSTRING([STRING],PATINDEX('%]%',[STRING])+1,LEN([STRING])))

)b(start)

CROSS APPLY (SELECT RTRIM(SUBSTRING(start,1,PATINDEX('%[[]%',start)-1))

)c(fin);

GO

-- IF FROM A VARIABLE

DECLARE @STRING AS VARCHAR(70) = '[N] 18. Is the C.B. removed from the panel? [Yes / No]';

SELECT RTRIM(SUBSTRING(start,1,PATINDEX('%[[]%',start)-1))

FROM (VALUES(LTRIM(SUBSTRING(@STRING,PATINDEX('%]%',@STRING)+1,LEN(@STRING))))

)a(start);

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Nomvula Hall of Fame Points: 3778 More actions · Answer 5

SSCray and Cadavre

you guys are rock stars!!! it working perfectly

thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Assuming a consistent format for the string:

declare @TestStr varchar(64) = '[N] 18. Is the C.B. removed from the panel? [Yes / No]';

select

patindex('%]%', ltrim(@TestStr)), patindex('% [[]%', @TestStr),

@TestStr,

ltrim(rtrim(substring(@TestStr,patindex('%]%', @TestStr) + 1, patindex('% [[]%', @TestStr) - patindex('%]%', @TestStr))));

get part of the string in between square brackets

Cookies on SQLServerCentral