Pattern-based split string

re: Pattern-based split string

Chris Law — Tue, 04 Sep 2007 23:26:42 GMT

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

re: Pattern-based split string

Adam Machanic — Mon, 10 Sep 2007 19:12:00 GMT

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

re: Pattern-based split string

Asghar — Thu, 29 Nov 2007 04:43:43 GMT

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

re: Pattern-based split string

Adam Machanic — Tue, 04 Dec 2007 15:32:18 GMT

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

re: Pattern-based split string

Phani Kumar — Mon, 08 Sep 2008 12:36:54 GMT

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

re: Pattern-based split string

shiburaj — Wed, 15 Apr 2009 09:02:19 GMT

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.

re: Pattern-based split string

Ngl — Mon, 24 Aug 2009 03:20:10 GMT

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

Eg.

The result of split function is :

Item

=====

001

Items2

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

How can I do that?

Thanx

re: Pattern-based split string

Adam Machanic — Mon, 24 Aug 2009 16:24:17 GMT

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.

re: Pattern-based split string

Mohan Goud — Wed, 28 Apr 2010 06:55:02 GMT

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