Splitting a String in SQL - Help Please!

  • Hi there! I'm struggling to split a string.. hope somebody can help me. Here's a description of my problem:

    CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door

    I need to access the first two parts of the above string.

    CSCAR009/0001/001

    PASPBD

    I can use left() to get at the first part. It is the same length in all rows.

    However this cannot be said for the second. It is a code which changes from row to row, and isn't the same length.

    The description on the right also changes and doesn't share the same format even with the same code (user inputted field). I am not interested in this field.

    I have tried using the substring function but the third part of the string pops in with the second which messes me up.

    I believe what I need is to use a split command on the string to get access to the first 2 parts. What I want is to assign these parts to seperate variables in ASP (the SQL statement will be ran from there too) enabling me to use the data.

    I found a guide on inserting a User defined Split function, but am at a loss on how to use it for my purpose. The examples given only show using it in a where clause with an IN performed.

    UDF Split:

    CREATE FUNCTION dbo.Split

    (

    @List nvarchar(2000),

    @SplitOn nvarchar(5)

    )

    RETURNS @RtnValue table

    (

    Id int identity(1,1),

    Value nvarchar(100)

    )

    AS

    BEGIN

    While (Charindex(@SplitOn,@List)>0)

    Begin

    Insert Into @RtnValue (value)

    Select

    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

    End

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

    Return

    END

    I rarely post on forums asking for help but this problem has eaten up my afternoon.

    Thank you in advance for any help offered.

  • If the -'s are there in every string, you can use CHARINDEX to extract the middle piece.

    DECLARE @String varchar(60)

    SET @String = 'CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door'

    SELECT LEFT(RIGHT(@String,LEN(@String)-18),(CHARINDEX('-',RIGHT(@String,LEN(@String)-18) ) )-1)

    The best thing about this method is that it can be used in any query, replacing @string with your field name.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Convert table split function to scalar one.

    Add another parameter - @ElementNo and return only value from the table within function with correlated ID.

    The you may use it like this:

    SELECT dbo.SplitScalar(StringValue, '-', 1), dbo.SplitScalar(StringValue, '-', 2)

    FROM ....

    _____________
    Code for TallyGenerator

  • greig.burrows (9/12/2008)


    Hi there! I'm struggling to split a string.. hope somebody can help me. Here's a description of my problem:

    Ugh... UDF's... While Loops... all good ways to slow your code down. Before I show you how to do this, I need to know a couple of things so I can decide which method to show you...

    1. Are you just splitting a single parameter (Sergiy is on the right track for this), or are you splitting a whole column of these?

    2. If you're splitting a whole column, I need to know what the primary key of the table is. It would also be handy to have more than one piece of data. Please take a look at the link in my signature below on how to correctly list the data to make it easier for me to help you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I have a library of UDfs that do exactly what you are looking for and much, much more. however, I sense your aversion to UDFs so I am not sure how you want to go.

    There was an article here in SQL Server Central a while ago about "Tally Tables" Using a "Tally" or numbers table will get you where you want to go without loops or UDFs.

    Respond back if you want to check out my UDFs.

    -M

  • Funny you should bring that article up... 🙂

    http://www.sqlservercentral.com/articles/TSQL/62867/

    But the desired method to do the split depends on my question... Is this a single parameter being split or does a whole column need to be split?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Oh hell... so long as I'm being self-indulgent...

    [font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font]

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    But, what I'd really like to know is what I've asked a couple of times now... is this to split a single parameter or a whole column of information? The performance of the solution is dependent on that, a lot.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No doubt RBAR is going to occur with UDFs. The tally table idea is going to be a join. The UDF will defintely work. So what do you want? Pretty or Guts & Glory?

    -M

  • Performance 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, performance is good.

    But did you see a solution for splitting string with quoted values?

    Like in CSV files?

    _____________
    Code for TallyGenerator

  • Absolutely... what do you want to do? Split a true CSV parameter or a tru CSV column in a poorly formed table that needs a bit of normalization? Heh... or do you want to import a properly formed true CSV file?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Use the Tally table, join to it, then use intrisinc SQL functions because you will have postive indexes to work with. Good luck.

  • Mike DiRenzo (9/13/2008)


    Use the Tally table, join to it, then use intrisinc SQL functions because you will have postive indexes to work with. Good luck.

    Who's that directed to, Mike?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Greig,

    When you get a chance... all I need to know is are you splitting a single parameter or do you need to split a whole column of these? If a whole column, then I need to know what the PK of the table is, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (9/13/2008)


    Absolutely... what do you want to do? Split a true CSV parameter or a tru CSV column in a poorly formed table that needs a bit of normalization? Heh... or do you want to import a properly formed true CSV file?

    I've got set of rows coming from "a properly formed true CSV file".

    Something like this:

    '"Pick-up",,"",1234,"2008-08-24", "2008-08-24","17"", unknown brand"'

    '"""Next Day"" delivery",5.00,"9,5"" box",10,"2008-09-01", "2008-09-11","9"" nails"'

    You understand, it must come to (for 2nd line):

    "Next Day" delivery

    5.00

    9,5" box

    10

    2008-09-01

    "2008-09-11"

    9" nails

    _____________
    Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply