Remove Pattern in sql query

  • hi guys,

    this is my first post here:-D. Hope someone can help me. I have a field in my table with data that looks like this:

    PO_x0020_416G_x002F_484A

    S_x0020_8292

    I need to strip out the _x002F_ and x_0020_ etc and the final result must look like this:

    PO 416G 484A

    S 8292

    Please help me write a function to do this. Thanks in advance

  • Nitesh, apart from _x002F_ and x_0020_ , are there any other pattern that might come in the string??

  • Also as u are brand new to this forum, i would recommend you going through this following article 🙂

    CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

    so please post:

    1. Table Structures - CREATE TABLE scripts

    2. Constraints or Indexes in the table, - CREATE SCRIPTS

    3. SAMPLE DATA - INSERT INTO TABLE scripts

    4. Desired output - some visual representation of this.

    When u do so, i am sure a lot of us will help u instantly...

    C'est Pras!

  • If '_x0020_' and '_x002f_' are going to be the only patterns that need to be replaced, then this following code with do the trick for you!

    First, check out how i set up the environment by providing the Tables and the Sample data!

    IF OBJECT_ID('TEMPDB..#Table') IS NOT NULL

    DROP TABLE #Table

    CREATE TABLE #Table

    (

    String VARCHAR(128)

    )

    INSERT INTO #Table (String)

    SELECT 'PO_x0020_416G_x002F_484A' UNION ALL

    SELECT 'S_x0020_8292' UNION ALL

    SELECT 'ABC_x0020_DEF_x0020_GHI_x002f_' UNION ALL

    SELECT 'A1B2_x0020_D3E44_x0020_5FR'

    Now for the code that will strip '_x0020_' and '_x002f_' from the string column:

    SELECT REPLACE(REPLACE(String,'_x0020_',' '),'_x002f_',' ') REPLACED_DATA FROM #TABLE

    Hope this gets you started. If not, then we are awating your clear requirements!

    Cheers!

  • thanks for the replies guys. sorry about not following etiquette.

    there could be other patterns like _x0002c etc. So I can't really do a replace:(

  • guys I've done this scalar function which gives me the first and last part of the string:

    for example P0_x002_3345

    will give me PO3345

    but for ones like this:

    PO_x002e_5567_3333

    I need to get PO 5567 3333

    and I only get

    PO3333

    ALTER function [dbo].[ExtractPONumber](@Text as varchar(8000))

    returns varchar(8000)

    as

    begin

    declare @Part1 varchar(8000);

    declare @Part2 varchar(8000);

    if LEN(@Text) > 0

    begin

    while (PATINDEX('%x002%',@Text) > 1)

    begin

    set @Text =

    replace(@Text,

    'x002',

    '')

    end

    set @Part1 = CASE WHEN CHARINDEX('_',@Text) > 0 THEN LEFT(@Text,CHARINDEX('_',@Text)-1) ELSE NULL END

    --LEFT(@Text,CHARINDEX('_',@Text)-1)

    set @Part2 = REPLACE(@Text,@Part1,'')

    set @Part2 = REVERSE(@Part2)

    set @Part2 = CASE WHEN CHARINDEX('_',@Part2) > 0 then LEFT(@Part2, CHARINDEX('_',@Part2)-1) else @Part2 END

    set @Part2 = REVERSE(@Part2)

    --set @Text = @Part1 + REPLACE(@Part2,'_','')

    end

    return @Part1 + @Part2--REPLACE(@Part1 + @Part2,'_',' ')

    end

  • Nitesh, another question, does your pattern always start and end with underscore ( _ ) ?

  • Hi ColdCoffee,

    yes it does always start and end with an _ .

  • Nitesh, here is a function that i coded for your requirement. I have used WHILE loop which is RBAR practice. There are lot of set-based code for this, which i am unaware and un-tried off. Probably lets wait for the others to jump in a provide an absolute fast code. Until then, this code wud suffice your needs!

    Check it out: Inline with the sample data i have give, this will work out for you

    IF OBJECT_ID (N'dbo.Replace_A_Pattern', N'FN') IS NOT NULL

    DROP FUNCTION dbo.Replace_A_Pattern;

    GO

    CREATE FUNCTION dbo.Replace_A_Pattern(@ToBeReplacedString VARCHAR(128),@FromString VARCHAR(128))

    RETURNS VARCHAR(128)

    AS

    BEGIN

    DECLARE @START INT

    DECLARE @END INT

    DECLARE @LENGTH INT

    SET @START = 1

    SET @END = DATALENGTH(@FromString)

    SET @LENGTH = DATALENGTH(@ToBeReplacedString)

    WHILE @START <= @END

    BEGIN

    DECLARE @POS INT

    SET @POS = CHARINDEX(@ToBeReplacedString,@FromString)

    IF @POS > 0

    /* The hardcoding of 7 in following STUFF function is for your requirement

    for general purpose, it has to be @LENGTH variable

    */

    SELECT @FromString = STUFF(@FromString , @POS , 7,' ')

    SET @START = @START + 1

    END

    RETURN @FromString

    END;

    GO

    Now , execute the funtion:

    SELECT String,dbo.Replace_A_Pattern('_x002',String) Stripped_String FROM #Table

    Now lets check out the results along with the input string:

    String Stripped_String

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

    PO_x0020_416G_x002F_484A PO 416G 484A

    S_x0020_8292 S 8292

    ABC_x0020_DEF_x0020_GHI_x002f_ ABC DEF GHI

    A1B2_x0020_D3E44_x0020_5FR A1B2 D3E44 5FR

    Hope this helps you! Tell us if this code did well!

    Cheers!

  • thank you so much ColdCoffee. This function is absolutely perfect for my requirement. and it is quite quick. 4 secs for 1000 records. you are a master;-)

  • You're welcome, nitesh..:-)

  • Hi again, is there anyway I can add to your reputation or something on this forum:-)

  • I'm afraid we dont have any..

  • niteshrajgopal (5/18/2010)


    Hi again, is there anyway I can add to your reputation or something on this forum:-)

    Heh... not to worry. CC is building his reputation where it counts. 😉

    --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)

  • {edit} Sorry... had a bug in the code and had to remove this post.

    {edit} {edit} Heh... it turned out to not be a bug. The Tally table code ran so fast I didn't think that it actually ran. 😀 It surprised even me. :w00t:

    --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)

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

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