Post reply

patindex alternative in SSIS

  • August 22, 2013 at 11:23 am

    #281556

    i need to implement the below code in ssis.

    declare @value nvarchar(200);

    set @value='EN 60325';

    WHILE @value LIKE '%[^0-9]%'

    SET @value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @value), 1), '')

    select @value

    output-> 60325

    Mainly i need alternative to patindex.

    Thanks in advance!

  • August 22, 2013 at 1:30 pm

    #1644109

    There is no direct alternative for patindex in the derived column. So you can either use a script component with .NET, or stick with SQL.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • August 22, 2013 at 4:43 pm

    #1644193

    If you can use CLRs you could look at mdq.regexmatches. See this thread.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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