Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 7th, 2007, 03:47 AM
Authorized User
 
Join Date: Sep 2006
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to VerbatimBOT Send a message via Yahoo to VerbatimBOT
Default Get something like SUBSTRING in SQL?

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
__________________
Aleksandar Dragosavac
Belgrade, Serbia
 
Old May 7th, 2007, 03:57 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old May 7th, 2007, 04:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 7th, 2007, 04:51 AM
Authorized User
 
Join Date: Sep 2006
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to VerbatimBOT Send a message via Yahoo to VerbatimBOT
Default

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
 
Old May 7th, 2007, 11:28 AM
Authorized User
 
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 8th, 2007, 02:36 AM
Authorized User
 
Join Date: Sep 2006
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to VerbatimBOT Send a message via Yahoo to VerbatimBOT
Default

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
 
Old May 9th, 2007, 04:53 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old May 10th, 2007, 02:15 AM
Authorized User
 
Join Date: Sep 2006
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to VerbatimBOT Send a message via Yahoo to VerbatimBOT
Default

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
 
Old May 11th, 2007, 01:15 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old May 11th, 2007, 05:06 PM
Authorized User
 
Join Date: Sep 2006
Posts: 37
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to VerbatimBOT Send a message via Yahoo to VerbatimBOT
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
substring in c# sudhirbharti C# 1 February 21st, 2008 01:29 PM
Substring-after collation bonekrusher XSLT 2 May 11th, 2007 09:31 AM
SubString prasanta2expert Access VBA 1 November 17th, 2006 10:04 AM
HELP - Substring question savoym C# 2 April 22nd, 2005 10:47 AM
Substring in Datagrid lily611 ADO.NET 5 July 22nd, 2004 02:57 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.