CODEWITHSUNDEEP
sqlsql-servert-sql
Iverson Wang
Iverson Wang

Reputation: 187

Retrieve several words in the string in T-SQL

I have a string like '(ST1) today is Tuesday,(ST2)--CCC,yao (ST3)'.I would like to retrieve all words that start with ST and then insert into a temp table. In this case, the temp table will have 3 rows, include ST1, ST2, and ST3. Is there any good way to achieve this goal? Thank you. I am using SQL Server 2008 and T-SQL is my language.

Upvotes: 0

Views: 61

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

You can also grab a copy of NGrams8K and do this: 

DECLARE @yourstring varchar(100) = '(ST1) today is Tuesday,(ST2)--CCC,yao (ST3)';

SELECT item = SUBSTRING(token, 2, 3)
FROM dbo.NGrams8k(@yourstring, 5)
WHERE token LIKE '(%)';

EDIT: How to handle values longer than 3:

DECLARE @yourstring varchar(100) = '(ST1) today is Tuesday,(ST2)--CCC,yao (ST11)!! (ST999)';

WITH gramSizes(s,ng) AS (SELECT 3, 5 UNION ALL SELECT 4, 6 UNION ALL SELECT 5, 7)
SELECT item = SUBSTRING(token, 2, s)
FROM gramSizes
CROSS APPLY dbo.NGrams8k(@yourstring, ng)
WHERE token LIKE '(%)'AND token NOT LIKE '%) '

Results:

item
-----
ST1
ST2
ST11
ST999

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

Assuming that each "word" is what you have in between parenthesis you can use a string splitter for this quite easily. I am using the splitter from Jeff Moden at sql server central.com. You can find an article, discussion and code here. http://www.sqlservercentral.com/articles/Tally+Table/72993/

There are plenty of other great splitters out there. Aaron Bertrand has an excellent article and collection of them here. http://sqlperformance.com/2012/07/t-sql-queries/split-strings

declare @SomeString varchar(100) = '(ST1) today is Tuesday,(ST2)--CCC,yao (ST3)'

select s.Item
from dbo.DelimitedSplit8K(replace(@SomeString, ')', '('), '(') s
where Item like 'ST%'

Upvotes: 1

Related Questions