SQL Server split function

This is used to "use list as a table", i.e. take a list of comma-separated values, and run queries on it.
Credits due: look at the second post on http://searchvb.techtarget.com/tip/0,289483,sid8_gci932171,00.html
 
CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##') --note second item has embedded #
select * from fnSplit('1 22 333 444 5555 666', ' ')
posted @ Thursday, April 12, 2007 6:54 AM
Print

Comments on this entry:

# re: SQL Server split function

Left by Daniel at 1/16/2008 4:15 PM
Gravatar
Thanks, this is very useful.
/Daniel

# re: SQL Server split function

Left by Angel Eyes at 1/16/2008 4:32 PM
Gravatar
Thanks, Daniel.

# re: SQL Server split function

Left by bylove at 5/7/2008 10:48 PM
Gravatar
thanks

# re: SQL Server split function

Left by Rachana at 6/13/2008 9:14 AM
Gravatar
Thanks a lot

# re: SQL Server split function

Left by falguni at 7/15/2008 8:46 PM
Gravatar
Thanks a lot. Its very useful.

# to falguni

Left by angel eyes at 7/15/2008 8:59 PM
Gravatar
Glad to have helped :-)

# re: SQL Server split function

Left by angel eyes at 7/26/2008 10:04 AM
Gravatar
Sure thing.

# re: SQL Server split function

Left by Andrew McGimpsey at 7/29/2008 2:47 PM
Gravatar
This is great! :)

If I then want to get back only the nth row from the resulting table how would I get it? I would presume there is some sort of rownum function that we can use?

# re: get back only the nth row from the resulting table

Left by angel eyes at 7/29/2008 5:16 PM
Gravatar
Assuming you want to keep the resulting table, you can add RowNumber(), like this:
select ROW_NUMBER() over (order by item) as RowNum, * from fnSplit('1,22,333,444,,5555,666', ',')

And then get the 4th row like this:
select * from
(select ROW_NUMBER() over (order by item) as RowNum, * from fnSplit('1,22,333,444,,5555,666', ',')) tt
where RowNum = 4

# re: SQL Server split function

Left by gsd at 8/1/2008 6:51 AM
Gravatar
Good f(x)

# re: SQL Server split function

Left by Ali at 9/17/2008 7:37 AM
Gravatar
Hi
I was write this sp :

UPDATE TBLMessages
SET Location = @Location, FolderID = @FolderID
WHERE (dbo.TBLMessages.MsgID in (SELECT item FROM dbo.FnSplit(MsgID,',')))

But It Updates All Records !!!

# re: Ali

Left by angel eyes at 9/17/2008 9:03 AM
Gravatar
Ali, your query doesn't make much sense. what were you trying to do, and what does the MsgID field contain?

# re: SQL Server split function

Left by Matt at 9/26/2008 6:55 PM
Gravatar
If I were trying to use this to split a column that I select, how would I do that?

For example, say we have some customer numbers that are set up in a special fashion like 000-xxx-xxx, and others that are set up like normal numbers areacode-prefix-number, and I want to throw that into a case when and pull a list of how many users we have set up like that, kind of like this:

SELECT SUM(CASE WHEN (SELECT item FROM (SELECT ROW_NUMBER() OVER (ORDER BY item) AS RowNum, * FROM fnSplit(SELECT dbo.CUSTOMER.PHONE_NUM FROM dbo.CUSTOMER WHERE CUSTOMER.CUST_ID=1234, '-')) drvTbl WHERE RowNum = 1) THEN 1 ELSE 0 END) AS IRREGULAR_COUNT FROM dbo.CUSTOMER

The fnSplit function doesn't allow me to throw a select statement in it (or maybe I'm just missing something here).

Any ideas?

# re: SQL Server split function

Left by Matt at 9/30/2008 12:59 AM
Gravatar
Yea, I figured that out after banging my head against the wall for an hour or so. I have a tendancy to overthink things sometimes, haha.

Thanks for the reply, and the great blog.

# re: SQL Server split function

Left by Marcie at 10/15/2008 4:12 PM
Gravatar
Is there a way to use fnsplit on a column that is selected?

For example:
select * from dbo.fnsplit(select prov_title from provider where prov_title > ' ')

This doesn't work.

# Marcie

Left by angel eyes at 10/15/2008 8:24 PM
Gravatar
Hey Marcie.
To my knowledge, you cannot use the function like that, in a query.
You can use a cursor to do the same thing, but of course you'll have to watch the performance.

# re: SQL Server split function

Left by fatty0860 at 11/11/2008 9:33 AM
Gravatar
Thanks!
It's very useful to me.

# re: SQL Server split function

Left by Baljeet Singh at 12/29/2008 11:52 AM
Gravatar
Thanks a Lot.

# To: fatty0860 and Baljeet Singh

Left by Angel Eyes at 12/29/2008 8:17 PM
Gravatar
Sure, glad to be of help.

# re: SQL Server split function

Left by Devil at 3/5/2009 4:01 PM
Gravatar
Hello,

how can I use this function with a cursor?

I want to use this function like this example:
select * from dbo.fnsplit(select prov_title from provider where prov_title > ' ')

# re: club penguin

Left by Angel Eyes at 3/26/2009 12:15 PM
Gravatar
Thanks, Club Penguin, it means a lot!

# re: SQL Server split function

Left by porno izle at 3/26/2009 1:41 PM
Gravatar
Thanks!
It's very useful to me.

# Turkey Tourism

Left by tourist book at 3/26/2009 4:28 PM
Gravatar
Thanks for writing.but please letters be more big.:)

# re: SQL Server split function

Left by Diyarbakir at 3/26/2009 6:04 PM
Gravatar
Thanks you

# re: SQL Server split function

Left by pornoizle at 3/26/2009 6:20 PM
Gravatar
thanks you

# re: SQL Server split function

Left by oyun indir at 3/26/2009 7:23 PM
Gravatar
Thank you very useful information.

# re: SQL Server split function

Left by türk porno izle at 3/26/2009 7:58 PM
Gravatar
thanks a lot..

# re: SQL Server split function

Left by ali veli at 3/26/2009 9:07 PM
Gravatar
Very thanks.

# re: SQL Server split function

Left by Downloadic at 3/26/2009 9:29 PM
Gravatar
Very nice, thanks.....

# re: SQL Server split function

Left by SMF Destek at 3/26/2009 10:25 PM
Gravatar
Hı :) Thank you post...

SMF Destek

# re: SQL Server split function

Left by Porno Sikiş at 3/26/2009 10:31 PM
Gravatar
Thanks for articles.

# re: SQL Server split function

Left by free wordpress themes at 3/26/2009 10:56 PM
Gravatar
Sorry my english firstly, i read well but i cant write :), So its useful post for me and i bookmarked your blog.

wordpress themes

web tasarım

# re: tourist book

Left by Angel Eyes at 3/27/2009 12:16 PM
Gravatar
I'll change the fonts, or the whole theme :-)

# re: SQL Server split function

Left by worldmp3 at 3/27/2009 1:59 PM
Gravatar
Thank you for information.

# re: SQL Server split function

Left by Sezer DEGE at 3/28/2009 12:19 PM
Gravatar
useful article, many thanks.


Daily Linux News
web hosting

# re: SQL Server split function

Left by francisco at 4/10/2009 12:23 AM
Gravatar
i have modified to return item at specified index:

ALTER FUNCTION [dbo].[split](@sInputList VARCHAR(8000),@sDelimiter VARCHAR(8000) = ',',@Index INT)
RETURNS NVARCHAR(4000)
BEGIN
DECLARE @sItem VARCHAR(8000)
DECLARE @retval NVARCHAR(4000)
DECLARE @tempIndex INT
DECLARE @List TABLE(item VARCHAR(8000))
SET @tempIndex = 0
WHILE Charindex(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT @sItem = Rtrim(Ltrim(Substring(@sInputList,1,Charindex(@sDelimiter,@sInputList,0) - 1))),
@sInputList = Rtrim(Ltrim(Substring(@sInputList,Charindex(@sDelimiter,@sInputList,0) + Len(@sDelimiter),
Len(@sInputList))))
IF Len(@sItem) > 0
INSERT INTO @List
SELECT @sItem
IF (@tempIndex = @Index)
SET @retval = @sItem
SET @tempIndex = @tempIndex + 1
END
IF Len(@sInputList) > 0
INSERT INTO @List
SELECT @sInputList -- Put the last item in
IF (@tempIndex = @Index)
SET @retval = @sItem
RETURN @retval
END
GO

# re: SQL Server split function

Left by re: francisco at 4/10/2009 11:09 AM
Gravatar
Way to go, Fransisco, Thanks for letting us know about it!

# re: SQL Server split function

Left by Tobar at 4/22/2009 6:46 PM
Gravatar
This doesn't work if space is your actual separator. ie there should be a NULL item, ala the first test, in the third test. As written extra spaces are discarded.

# re: SQL Server split function

Left by Tobar at 4/22/2009 8:55 PM
Gravatar
By "this" I was referring to the original post.

Here is a fix. Why in the world MS thought it smart to not count a space in the len() function is beyond me.

drop function dbo.fnSplit

go

CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1),
@sInputList=SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+DATALENGTH(@sDelimiter),DATALENGTH(@sInputList))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
ELSE IF LEN(@sItem) = 0
INSERT INTO @List SELECT NULL
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##') --note second item has embedded #
select * from fnSplit('1 22 333 444 5555 666', ' ')

# re: SQL Server split function

Left by Ali at 5/8/2009 6:44 PM
Gravatar
Thanks ,for articles.

# EMİRCAN OTO KİRALAMA ANKARA

Left by Cihan at 6/22/2009 8:34 AM
Gravatar
Thank you

# LÜKS OTO KİRALAMA ANKARA EMİRCAN

Left by Ciko at 6/22/2009 8:39 AM
Gravatar
mersi şekerim

# re: SQL Server split function

Left by Sunshine Coast Web Designer at 7/8/2009 7:21 AM
Gravatar
Nice work. Was just about to write this myself then thought I should see if somebody else had already posted a solution before I reinvented the wheel. Now that's the proper meaning of resuable code.

# re: SQL Server split function

Left by güncel blog at 7/8/2009 2:25 PM
Gravatar
thanks admins very nicess

# re: SQL Server split function

Left by video seyret at 7/8/2009 2:27 PM
Gravatar
thanks you goods

# re: SQL Server split function

Left by Lida at 7/18/2009 9:52 AM
Gravatar
Good on your

# re: SQL Server split function

Left by Lida at 7/18/2009 9:54 AM
Gravatar
THanks a lot

# re: SQL Server split function

Left by Lida at 7/18/2009 9:54 AM
Gravatar
Lot name a

# prefabrik fiyatları

Left by prefabrik fiyatlari at 8/7/2009 12:35 PM
Gravatar
Thanks for the reply, and the great blog.

# re: SQL Server split function

Left by Diyarbakir at 8/12/2009 10:10 PM
Gravatar
Thanks for the reply, and the great blog.

# re: SQL Server split function

Left by Urls at 8/12/2009 10:10 PM
Gravatar
Good on your

# re: SQL Server split function

Left by evden eve nakliyat at 9/9/2009 12:17 PM
Gravatar
very useful command.thanks for that

# re: SQL Server split function

Left by invention of lying online at 10/5/2009 2:34 PM
Gravatar
THanks a lot

# re: SQL Server split function

Left by Forum at 11/15/2009 8:32 PM
Gravatar
thanks admins very nicess

# re: SQL Server split function

Left by Sunil Kumar Sharma at 12/24/2009 10:56 AM
Gravatar
It's very help full to me thanks

# re: SQL Server split function

Left by koçluk at 12/30/2009 1:53 PM
Gravatar
very nice htanks

# re: SQL Server split function

Left by Clasamente Fotbal at 1/23/2010 2:18 PM
Gravatar
Well worth the read. Thanks for sharing this information. I got a chance to know about this.

# re: SQL Server split function

Left by usama at 2/20/2010 9:18 AM
Gravatar
love you

# re: SQL Server split function

Left by adult videolar at 2/28/2010 12:49 PM
Gravatar
thanks adminss good very nicess

# re: SQL Server split function

Left by SSdevi at 3/19/2010 10:36 AM
Gravatar
hi its very useful but how can i use the split function to search a full name with 3 type of names(first,middle,last) name?

Plz its urgent Reply needed

# re: SSdevi

Left by Angel Eyes at 3/19/2010 12:17 PM
Gravatar
I'm sorry, I didn't understand your question.

# re:SQL Server split function

Left by evden eve nakliyat at 5/23/2010 3:12 AM
Gravatar
thanks adminss good very nicess

# esya

Left by chris at 5/23/2010 3:19 PM
Gravatar
The first priority of the site administrator for your comments right know, thank you. Really very good information-sharing gives you.
Thanks in knowledge

# re: SQL Server split function

Left by Meksika biberi zayıflama hapı at 6/1/2010 3:01 PM
Gravatar
thanks for this article

# re: SQL Server split function

Left by rca ieftin 2011 at 6/22/2010 7:04 PM
Gravatar
It is good that you share this informations whit us.

# re: SQL Server split function

Left by web tasarım at 8/4/2010 1:45 PM
Gravatar
thank you man

# re: SQL Server split function

Left by ucuz web tasarım at 8/16/2010 2:30 AM
Gravatar
thanks for this article .

# re: SQL Server split function

Left by prefabricated at 8/26/2010 3:35 PM
Gravatar
Sending interactive content (changeable text, changeable images, forms, etc) to other users, websites and other applications. Like: if i want

# re: SQL Server split function

Left by havuz at 9/14/2010 11:45 AM
Gravatar
thanks for this article .

# re: SQL Server split function

Left by peruk at 9/14/2010 11:49 AM
Gravatar
thanks very much for sharing

# re: SQL Server split function

Left by Fenerbahçe Beşiktaş Maçını İzle at 9/14/2010 12:21 PM
Gravatar
thanks very much

# re: SQL Server split function

Left by Resim Paylas at 9/16/2010 2:53 AM
Gravatar
thank you best regards. Goods rapids.

# re: SQL Server split function

Left by sa at 10/5/2010 2:01 PM
Gravatar
great, thanks.

# re: SQL Server split function

Left by php görsel eğitim at 10/6/2010 9:35 PM
Gravatar
Thanks for article. Everytime watch blog like to read you

# çocuk montu

Left by zafer at 10/14/2010 12:23 PM
Gravatar
thank you best regards. Goods rapids.

# çocukmont

Left by zafer at 10/14/2010 5:29 PM
Gravatar
thanks very much by man

# re: SQL Server split function

Left by fotoğrafçı at 10/20/2010 1:40 PM
Gravatar
very good! thanks..

# re: SQL Server split function

Left by havuz at 10/21/2010 9:37 AM
Gravatar
well done man. very good sharing..

# re: SQL Server split function

Left by peruk at 10/21/2010 9:38 AM
Gravatar
thank you. very interesting

# re: SQL Server split function

Left by toptan takım elbise at 10/22/2010 9:20 PM
Gravatar
very well. thanks man

# re: SQL Server split function

Left by Suresh at 10/27/2010 9:33 AM
Gravatar
Super ...

# re: SQL Server split function

Left by web tasarım at 10/28/2010 9:04 PM
Gravatar
Thank you admin good article

# ozaygroup

Left by toptan takım elbise at 11/7/2010 5:35 PM
Gravatar
verytime watch blog like to read you

# elestirel haber

Left by maç özeti ve golleri at 11/7/2010 5:37 PM
Gravatar
Thanks for article.

# re: SQL Server split function

Left by prefabricated at 11/12/2010 9:50 AM
Gravatar
It would be great if I could just say "movie Batman" and it would search Batman on IMDB, or "google firefox" and etc. In fact, my Eee PC has this functionality, so it stands to reason that Firefox could to. Anyway, those are just some of my thoughts off the top of my head. I'm sure that with some more thought a really well-built voice command extension could be built.

http://www.karmod.eu

# re: SQL Server split function

Left by Rinkal at 11/19/2010 11:52 AM
Gravatar
Thanks... I found it really useful

# re: SQL Server split function

Left by auto hibride at 12/1/2010 1:03 PM
Gravatar

I'm afraid it's all a fraud. Unfortunately, almost a decade only everything revolves around money. People who until recently had an enviable character fall prey to these temptations. Unfortunately, society as a whole began to decline and only fault I see greed. We humans would be to think that there are many other things more important than money. And maybe we will be able to change something in our thinking.

# re: SQL Server split function

Left by Memocix at 12/11/2010 2:50 PM
Gravatar
Thanks Admin ,for articles.

# Business Letters

Left by Sales Letters Template at 12/13/2010 4:50 AM
Gravatar
As a business owner and sales professionals, I am sure we share many of the same problems. I am always have to write letters. I have to write a follow up letter, sales letter, thank you letter, welcome letter, and other legal and professional letters.

I have found a great Letter Template Tool that i would like to share with all business owners and Sales agents, reps, and professionals...

It has helped me save time and money...Check it out, and give it a try...

# re: SQL Server split function

Left by Cel mai ieftin RCA at 12/17/2010 11:10 AM
Gravatar
Greate article, congratulations !

# re: SQL Server split function

Left by experts comment at 12/19/2010 11:59 AM
Gravatar
clean and crisp solution..
http://www.a2zmenu.com/MySql/SQL-Split-Function.aspx

# re: SQL Server split function

Left by forex at 12/20/2010 2:49 PM
Gravatar
expression was very nice thank you Waiting for More

# re: SQL Server split function

Left by çilingir at 12/31/2010 2:59 PM
Gravatar
Unfortunately, society as a whole began to decline and only fault I see greed.

# re: SQL Server split function

Left by resimler at 1/10/2011 2:52 AM
Gravatar
thanks for blogg..

# re: SQL Server split function

Left by web tasarım at 1/11/2011 6:20 PM
Gravatar
nice good job.

# re: SQL Server split function

Left by fotoğraf çekimi at 1/18/2011 11:41 AM
Gravatar
thank you very much

# re: SQL Server split function

Left by samsun at 1/23/2011 1:03 PM
Gravatar
Gravatar
Thanks a lot

# re: SQL Server split function

Left by web tasarımı at 1/28/2011 9:52 AM
Gravatar
Thank you admin good article. Good job

web tasarım

# re: SQL Server split function

Left by web tasarımı at 1/28/2011 9:53 AM
Gravatar
Thank you admin good article. Good job

http://www.celilcan.com

# re: SQL Server split function

Left by Webmaster Blog at 1/28/2011 9:56 AM
Gravatar
Thank you admin

# re: SQL Server split function

Left by umre kayıtları at 1/29/2011 8:22 PM
Gravatar
Thank you admin good article. Good job

# re: SQL Server split function

Left by gelinlikler at 2/1/2011 5:44 PM
Gravatar
More Waiting for you to continually write articles I am following a very successfull

# re: SQL Server split function

Left by Ed at 2/2/2011 11:16 PM
Gravatar
FYI:
If you use cross / outer apply you can join it to a table row by row without a cursor

http://www.mssqltips.com/tip.asp?tip=1958

select * from CF1 cf cross apply fnSplit(value_text,'$,$')
where value_text like '%$,$%'

# re: SQL Server split function

Left by çiçek sipasi gönder at 2/7/2011 12:45 PM
Gravatar
Thanks you very much! Wonderful comments, i am reading this blog and very happy now!

Thanks again!

# re: SQL Server split function

Left by sanalcadı at 2/8/2011 4:46 PM
Gravatar
Thanks!
It's very useful to me.

# re: SQL Server split function

Left by mini fridge at 2/9/2011 1:10 AM
Gravatar
As someone who is learning about SQL Server, the code listed is useful.

# re: SQL Server split function

Left by web tasarım ankara at 2/11/2011 11:55 PM
Gravatar
thank you for sharing post

# re: SQL Server split function

Left by mermer lavabo at 2/12/2011 11:42 AM
Gravatar
otel, otel rehberi, otel fiyatları, bayilikler, ucuz otel, tatil, tatil yerleri, konaklama, otel rezervasyon, afton otel, erzurum otel, sivas otal, oteller, otel turları, otel ajansları, istanbul oteller, türkiye oteller, rezervasyon, giriş işlemleri, otel rehberleri, anlaşmalı oteller, otel rehberi

# re: SQL Server split function

Left by tercüme at 2/16/2011 6:15 PM
Gravatar
thank you nice post tercüme hizmetleri

# re: SQL Server split function

Left by kocluk-egitimi at 2/17/2011 1:40 PM
Gravatar
Thanks for the given informations. These are really good and very useful informations. Everybody has to see that. So I will share this link with all my friends. Thanks

# re: SQL Server split function

Left by hazır beton at 2/17/2011 5:53 PM
Gravatar
When human life is indispensable to the settlement with the location of developing practical solutions, has left the right. Housing at the beginning of creating practical solutions that are ready-mixed concrete. Ready Mixed Concrete our time sine qua non of the construction sector.

# re: SQL Server split function

Left by fotoğraf çekimi at 2/17/2011 5:55 PM
Gravatar
Fotograf sanatçısı Sami Aker İstanbul da Modacı Fransız bir anneden doğdu ve şehrin moda merkezi nişantaşında yetişti, ilk orta ve liseyi Şişli Terakki Lisesinde tamamladıktan sonra İstanbul Üniversitesinden ve Marmara Universitesinden pazarlama ve image konusunda master derecesi ile mezun oldu.

# re: SQL Server split function

Left by otobüs firmaları at 2/19/2011 4:22 PM
Gravatar
Thanks for the given informations. These are really good and very useful informations. Everybody has to see that. So I will share this link with all my friends. Thanks

# re: SQL Server split function

Left by peruk at 2/19/2011 4:24 PM
Gravatar
Thanks you very much! Wonderful comments, i am reading this blog and very happy now!

# re: SQL Server split function

Left by cheap replica watches at 2/21/2011 11:57 AM
Gravatar
A great article written with great hard work...i must say....Thanks for posting this article. I am definitely tired of struggling to find relevant and intelligent commentary on this subject. Everyone nowadays seem to go to extremes to either drive home their viewpoint or suggest that everybody else in the globe is wrong. Thanks for your concise and relevant insight a great work of your which shows...

# re: SQL Server split function

Left by Go Seeq Search Engine at 2/21/2011 11:58 AM
Gravatar
I just love your blog.Thanks for posting it.i have something that someone comeback again….there is a lot of useful information a person can get from here…I must say,well done...i visited it daily,what a colourful blog it is....colours attracted me a lot....i visited it daily,what a colourful blog it is.... a very decent and nice blog...i like it so very much...

# re: SQL Server split function

Left by web tasarım ankara at 2/23/2011 1:49 PM
Gravatar
thanks for sharing...

# re: SQL Server split function

Left by izmir-reiki at 2/25/2011 6:22 PM
Gravatar
Thanks for article. Everytime watch blog like to read youİzmir reiki<br />

# re: SQL Server split function

Left by izmir-kisisel-gelisim-merkezi at 2/25/2011 6:26 PM
Gravatar
If I were trying to use this to split a column that I select, how would I do that?

For example, say we have some customer numbers that are set up in a special fashion like 000-xxx-xxx, and others that are set up like normal numbers areacode-prefix-number, and I want to throw that into a case when and pull a list of how many users we have set up like that, kind of like this:

SELECT SUM(CASE WHEN (SELECT item FROM (SELECT ROW_NUMBER() OVER (ORDER BY item) AS RowNum, * FROM fnSplit(SELECT dbo.CUSTOMER.PHONE_NUM FROM dbo.CUSTOMER WHERE CUSTOMER.CUST_ID=1234, '-')) drvTbl WHERE RowNum = 1) THEN 1 ELSE 0 END) AS IRREGULAR_COUNT FROM dbo.CUSTOMER

The fnSplit function doesn't allow me to throw a select statement in it (or maybe I'm just missing something here).

Any ideas? İzmir kişisel gelişim merkezi

# re: SQL Server split function

Left by izmir-öğrenci-koçluğu at 2/25/2011 6:29 PM
Gravatar
This is great! :)

If I then want to get back only the nth row from the resulting table how would I get it? I would presume there is some sort of rownum function that we can use? İzmir öğrenci koçluğu

# re: SQL Server split function

Left by ev mobilya at 3/6/2011 8:27 PM
Gravatar
thanks very nicess good code

# re: SQL Server split function

Left by hair styles at 3/10/2011 9:34 PM
Gravatar
thanks adminss good

# re: SQL Server split function

Left by devlet hastane at 3/12/2011 10:54 AM
Gravatar
I want to throw that into a case when and pull a list of how many users we have set up like that, kind of like this:

# re: SQL Server split function

Left by Fx15 at 3/12/2011 11:51 AM
Gravatar
Thank you. great working
Comments have been closed on this topic.