Get something like SUBSTRING in SQL?

VerbatimBOT · May 7th, 2007, 03:47 AM

Hi!
I have in my table one column with data like

Code:

#3#6#9#4

Is there any chance to get an array (or substrings) of these digits in SQL? In .NET would not be a problem, but i need to do it in SQL.

Best regards,
Aleksandar

e-ducan Global Store
Programmer/Application developer
http://www.e-ducan.com
Belgrade, Serbia

joefawcett · May 7th, 2007, 03:57 AM

You can write a split function, there are many examples online. You can't have an array in SQL Server but it can return a table with a row for each data item.

--

Joe (Microsoft MVP - XML)

happygv · May 7th, 2007, 04:30 AM

VerbatimBOT,

Did you search here in this forum before posting it here? Take a look at split function equivalent

cheers

_________________________
- Vijay G
Strive for Perfection

VerbatimBOT · May 7th, 2007, 04:51 AM

To be completley honest, I didn't have time to search the forum.
I was in a hurry. Thank you very much for helping me.

All the best!

e-ducan Global Store
Programmer/Application developer
http://www.e-ducan.com
Belgrade, Serbia

sshelper · May 7th, 2007, 11:28 AM

Here's another link that may be able to help you:

http://www.sql-server-helper.com/fun...-to-table.aspx

Simply change the comma delimiter to a pound sign (#).

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

VerbatimBOT · May 8th, 2007, 02:36 AM

Yes, I used one simular function. If I new for DATALENGTH function, I would write my own. :)
But, it's great when you find out something new.

Cheers!

e-ducan Global Store
Programmer/Application developer
http://www.e-ducan.com
Belgrade, Serbia

robprell · May 9th, 2007, 04:53 PM

You could do it in PHP, Mysql, Pgsql, or Perl if you have access to those languages, but MSSql does not support the split function.

VerbatimBOT · May 10th, 2007, 02:15 AM

I know, but there are other things that could replace the SPLIT function...

e-ducan Global Store
Programmer/Application developer
http://www.e-ducan.com
Belgrade, Serbia

robprell · May 11th, 2007, 01:15 PM

the only way I can think of to do it with sql is with a cursor or function that has a cursor in it and itterate through field by field. If you come up with a solution please post it here.

VerbatimBOT · May 11th, 2007, 05:06 PM

Why would you need a cursor?
I have only one field with digits separated with a # character.
joefawcett was right, there are many examples online.
I used the following function

Code:

CREATE FUNCTION dbo.fnParseList
(
    @Delimiter CHAR,
    @Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
    DECLARE    @NextPos SMALLINT,
        @LastPos SMALLINT

    SELECT    @NextPos = 0

    WHILE @NextPos <= DATALENGTH(@Text)
        BEGIN
            SELECT    @LastPos = @NextPos,
                @NextPos =    CASE
                            WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
                            ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
                        END

            INSERT    @Result
                (
                    Data
                )
            SELECT    SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
        END

    RETURN
END

Programmer/Application developer
Belgrade, Serbia