String Dissect

  • I'm passing into a stored procedure from a multi select in Reporting Services:

    @sku = '000000610214615336 - Copper,000000610214615343 - Sage'

    I need to dissect the string and only get the numbers to use in a where statement

    The where statement is such:

    where sku = @SKU

    The table which stores SKU only like 000000610214615336,000000610214615343, etc

  • CREATE FUNCTION dbo.fnExtractDigitSequence

    (

    @ColumnDelimiter CHAR(1) = ',',

    @Filter VARCHAR(8000) = '[0-9]',

    @UserData VARCHAR(8000) = NULL

    )

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    DECLARE@Position INT,

    @ValidChar TINYINT,

    @LastValidChar TINYINT

    SELECT@Position = LEN(@UserData),

    @LastValidChar = 0

    WHILE @Position > 0

    SELECT@ValidChar =CASE

    WHEN SUBSTRING(@UserData, @Position, 1) COLLATE Latin1_General_BIN LIKE @Filter THEN 1

    ELSE 0

    END,

    @UserData =CASE

    WHEN @ValidChar = 1 THEN @UserData

    WHEN @LastValidChar = 0 THEN STUFF(@UserData, @Position, 1, '')

    ELSE STUFF(@UserData, @Position, 1, @ColumnDelimiter)

    END,

    @LastValidChar = @ValidChar,

    @Position = @Position - 1

    RETURNCASE

    WHEN @UserData LIKE @ColumnDelimiter + '%' THEN SUBSTRING(@UserData, 2, 7999)

    ELSE NULLIF(@UserData, '')

    END

    END

    Call with

    SELECTdbo.fnExtractDigitSequence(',', '[0-9]', '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')


    N 56°04'39.16"
    E 12°55'05.25"

  • Another alternative might be somethinng like:

    declare @aTest table (combinedString varchar(25))

    insert into @aTest

    select '1015Abc' union all

    select '1022' union all

    select '5157D2' union all

    select 'Db2' union all

    select '#' union all

    select '#d' union all

    select '' union all

    select '105#' union all

    select '205128 ' union all

    select null

    --select * from @aTest

    select combinedString,

    left(combinedString, patindex('%[^0-9]%',combinedString+'X') - 1) as [Numeric Part],

    substring(combinedString, patindex('%[^0-9]%',combinedString+'X'),25) as [Remainder]

    from @aTest

    /* -------- Sample Output: --------

    combinedString Numeric Part Remainder

    ------------------------- ------------------------- -------------------------

    1015Abc 1015 Abc

    1022 1022

    5157D2 5157 D2

    Db2 Db2

    # #

    #d #d

    105# 105 #

    205128 205128

    NULL NULL NULL

    */

    Edit:

    I asked a similar question in the MSDN Transact SQL forum here:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3036691&SiteID=1

    The solution that I gave was actually Mark's solution.

Viewing 3 posts - 1 through 2 (of 2 total)

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