Post reply

Eliminate characters from a string based on another string

  • December 17, 2014 at 6:49 am

    #299573

    I have a string 'ACDIPFJZ'

    In my table one of the column has data like

    PFAG

    ABCDEFHJMPUYZ

    KML

    JC

    RPF

    My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.

    My output should be:

    PFAG -- PFA (G Eliminated)

    ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)

    KML -- No data

    JC -- JC

    RPF -- PF (R Eliminated)

  • December 17, 2014 at 10:57 am

    #1765245

    The first way I can think of to even come close to emulating this behavior in SQL Server is to use REPLACE() to remove any characters that are NOT in your list - but that means doing a separate REPLACE() call for each and every character that is not in your original set (two if your SQL Server instance is case sensitive.) That stops being fun to write very quickly (although you could do it dynamically for a little less pain.)

    A much better way: Use PATINDEX() and STUFF().

    declare @ValidChars as varchar(13) = '%[^ACDIPFJZ]%';

    declare @TestString as varchar(20) = 'ABCDEFHJMPUYZ';

    while PatIndex(@ValidChars,@TestString) > 0

    set @TestString = STUFF(@TestString, PatIndex(@ValidChars, @TestString),1,'')

    select @TestString;

    I would probably make this a stored procedure if it's something you're going to use extensively (although you can just make it a SELECT statement if it's a one-time deal.)

    Inspired by http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server

  • December 17, 2014 at 11:06 pm

    #1765330

    Thanks a ton for the solution !!!:-):-):-) It worked :-):-):-)

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

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