THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Pattern-based split string

"hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion.

Typical string split functions, like this one that I wrote can handle only a single delimiter, e.g. a comma. But in the case of splitting for unique words you want all sorts of possible delimiters -- punctuation marks, white space including spaces, possibly numerics, etc.

I believe I've seen other, similar requests for getting unique words before, so I decided to solve the problem. I modified that split string function to accept a pattern of valid characters for the second argument. Anything NOT in the pattern will be treated as a delimiter. I figured that would be a bit more flexible than the other way around, so that people don't have to type in (or figure out) every single possible white space and/or punctuation character.

Anyway, I think the modification is pretty straightforward if you understand the previous function, so check it out and if you have any questions feel free to ask. Here is the function:

 

CREATE FUNCTION dbo.SplitStringPattern
(
@List TEXT,
@Pattern VARCHAR(50)
)
RETURNS @ReturnTbl TABLE (OutParam VARCHAR(20))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @LeftSplit VARCHAR(7998)
DECLARE @SplitStart INT SET @SplitStart = 0
DECLARE @SplitEnd INT
SET @SplitEnd = 7998

SET @Pattern = '%' + '[^' + RIGHT(@Pattern, LEN(@Pattern) - 1) + '%'

DECLARE @Delimiter CHAR(1)
SELECT @Delimiter = CHAR(MAX(Number))
FROM dbo.Numbers
WHERE CHAR(Number) LIKE @Pattern

SELECT @SplitEnd = MAX(Number)
FROM dbo.Numbers
WHERE (SUBSTRING(@List, Number, 1) LIKE @Pattern
OR Number = DATALENGTH(@List) + 1)
AND Number BETWEEN @SplitStart AND @SplitEnd

WHILE @SplitStart < DATALENGTH(@List) - 1
BEGIN
SET @LeftSplit = @Delimiter + SUBSTRING(@List, @SplitStart, @SplitEnd - @SplitStart) + @Delimiter

INSERT @ReturnTbl (OutParam)
SELECT LTRIM(RTRIM(SUBSTRING(@LeftSplit, Number + 1,
PATINDEX(@Pattern, SUBSTRING(@LeftSplit, Number + 1, LEN(@LeftSplit))) - 1)))
AS Value
FROM dbo.Numbers
WHERE Number <= LEN(@LeftSplit) - 1
AND SUBSTRING(@LeftSplit, Number, 1) LIKE @Pattern
AND SUBSTRING(@LeftSplit, Number + 1,
PATINDEX(@Pattern, SUBSTRING(@LeftSplit, Number + 1, LEN(@LeftSplit))) - 1) <> ''

SET @SplitStart = @SplitEnd + 1
SET @SplitEnd = @SplitEnd + 7998

SELECT @SplitEnd = MAX(Number) + @SplitStart
FROM dbo.Numbers
WHERE (SUBSTRING(@List, Number + @SplitStart, 1) LIKE @Pattern
OR Number+@SplitStart = DATALENGTH(@List) + 1)
AND Number BETWEEN 1 AND @SplitEnd - @SplitStart
END

RETURN
END

A note on the pattern for input: The pattern should be single-character based, and must be delimited with [ ]. Also, you must type in the actual characters in the pattern. I do not recommend using ranges; ranges cause strange side-effects because they tend to include (depending on collation) characters that you wouldn't expect to show up (e.g. characters with umlauts over them). So unless that's what you really want, don't use ranges.

Here's a usage example:

SELECT OutParam
FROM dbo.SplitStringPattern('This is a test.', '[abcdefghijklmnopqrstuvwxyz]')


OutParam
-------------
This
is
a
test

Enjoy!


Published Wednesday, July 12, 2006 10:31 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Law said:

Hi Adam,

I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,

e.g.

ID     Email

--     ----------

1      AS1 AS11

2      AS2 AS3 AS4 AS5

3      AS6 AS7

The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.

ID      EmailAddress

---     ------------------

AS1      abcstu@emc.com

AS2      abcstu2@emc.com

AS3      abcstu3@emc.com

AS4      abcstu4@em.com

AS5      abcstu5@emc.com

AS6      abcstu6@emc.com

AS7      abcstu7@emc.com

AS11     abcstu8@emc.com

I need to create a stored procedure or function that:

1. Selects an Email from the first table, based on a valid ID,

2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,

3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array

Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?

Many Thanks,

Chris Law

probetatester@yahoo.com

September 4, 2007 7:26 PM
 

Adam Machanic said:

Hi Chris,

Sure: Use a standard SplitString function (such as the one posted here: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/splitting-a-string-of-unlimited-length.aspx), and then use the following query:

SELECT j.EmailAddress
FROM
(
SELECT x.OutParam
FROM YourIdentifierTable i
CROSS APPLY dbo.SplitString(i.Email) x
WHERE i.ID = 123
) y
JOIN YourEmailAddressTable j ON y.OutParam = j.ID

September 10, 2007 3:12 PM
 

Asghar said:

Hi Adam this is a great function but I have got string with such as ("hello","sdfsfs",....)

My problem is how can I pass two parremeters such as , and "".

and how can I insert the values into a table

Thanks

November 28, 2007 11:43 PM
 

Adam Machanic said:

Asghar:

I think you need the Tokenizer (or perhaps a slight modification of it):

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/tokenize-udf.aspx

December 4, 2007 10:32 AM
 

Phani Kumar said:

Hi adam i am doing a search engine for my job portal... if user wnters a search string i have to give him related records using rank technology.. can you suggest me .how to pass a full string to a querry it should be matched by all columns and give me results..

ex: search string is .. i want jobs on c in alabama

i have to sarch this string in posting table

can you give me example please

September 8, 2008 8:36 AM
 

shiburaj said:

Hi Chris Law,

I had a similar requirement,

The query I used was

select

t2.userprofile_empcompid,

t2.userprofile_empid,

t1.email,

case when CHARINDEX ( '@' ,t1.email ) >0 then

left(t1.email,(CHARINDEX ( '@' ,t1.email  )-1)) else t1.email end a  ,

t2.userprofile_emailadd

from EPHR_Email t1

INNER JOIN userprofile t2

ON t1. CompId = t2.userprofile_empcompid

AND t1.EmpId = t2.userprofile_empid

hope it helps.

April 15, 2009 5:02 AM
 

Ngl said:

Please help me, how to insert the result of split function?

Eg.

The result of split function is :

Item

=====

001

Items2

55

I wanna insert the result above to table tbl_data(code,name,qty).

How can I do that?

Thanx

August 23, 2009 11:20 PM
 

Adam Machanic said:

Hi Ngl,

You'll have to pivot the results--using either the PIVOT function or CASE expressions. Google "sql crosstab" for a number of solutions.

August 24, 2009 12:24 PM
 

Mohan Goud said:

Hi NGL,

My requirement is how to split the strings in sqlserver 2005.

for example:

Vtable[Table Name]

Fname[column Name varchar(60)]

Aamina Yuusuf Dudub Bashiir----> Split this word as follows

                                Aamina

                                Yuusuf

                                Dudub

                                Bashiir

April 28, 2010 2:55 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement