Finding a word within a string

Question

Post reply

Finding a word within a string

Boby B Jacob

SSC Veteran

Points: 264
More actions
November 27, 2014 at 2:25 am

#150253

Hi,
i wanted to select a word within a string, ie; for example, "I have: a - doubt". In this string, i need to extract the word enclosed between: and -. Help please...
An example scenario:
CREATE TABLE #TEMP (STR VARCHAR(200))
INSERT INTO #TEMP VALUES('Forwarded to Approver - SantosM ::: Comment carried forward: ')
SELECT * FROM #TEMP
I need to extract SantosM from the column,STR....Thank you..

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

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Pulivarthi Sasidhar

SSCertifiable

Points: 6706

November 27, 2014 at 2:30 am

#1761224

Use CHARINDEX and SUBSTRING functions...

tripleAxe SSCertifiable Points: 5706 More actions · Answer 2

Hi Bobby,

You can do this using the String functions in T-SQL, specifically SUBSTRING and CHARINDEX. There are some links below to msdn that will get you started.

String Functions

http://msdn.microsoft.com/en-us/library/ms181984%28v=sql.90%29.aspx

SUBSTRING

http://msdn.microsoft.com/en-us/library/ms187748%28v=sql.90%29.aspx

CHARINDEX

http://msdn.microsoft.com/en-us/library/ms186323%28v=sql.90%29.aspx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

If you can provide some readily-consumable sample data, someone will be more likely to put together a query for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Boby B Jacob SSC Veteran Points: 264 More actions · Answer 4

ChrisM@Work (11/27/2014)
If you can provide some readily-consumable sample data, someone will be more likely to put together a query for you.

Hi Chris, scenario is made available by now....thank you

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Boby B Jacob (11/27/2014)
ChrisM@Work (11/27/2014)
If you can provide some readily-consumable sample data, someone will be more likely to put together a query for you.
Hi Chris, scenario is made available by now....thank you

It's only one sample - I expect you will be posting back shortly 😉

DROP TABLE #TEMP

CREATE TABLE #TEMP ([STR] VARCHAR(200))

INSERT INTO #TEMP VALUES('Forwarded to Approver - SantosM ::: Comment carried forward: ')

SELECT

*,

SUBSTRING([STR], x1.p1, x2.p2-x1.p1)

FROM #TEMP

CROSS APPLY (SELECT p1 = 1+CHARINDEX('-',[STR])) x1

CROSS APPLY (SELECT p2 = CHARINDEX(':',[STR],x1.p1)) x2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Boby B Jacob SSC Veteran Points: 264 More actions · Answer 6

Boby B Jacob

SSC Veteran

Points: 264

November 27, 2014 at 5:42 am

#1761291

It did wonders...Thank you, Chris