Post reply

SP with substring

  • September 25, 2009 at 4:23 pm

    #211419

    Hi,

    i have data separated in a row with '/'.

    I need to delete everything before the '/' in every data of the row.

    Can it be done ??

    thanks in advance.

  • September 25, 2009 at 10:38 pm

    #1058724

    declare @result varchar(20)

    select @result = 'testdb/sysdb'

    select PATINDEX('%/%',@result)

    select left(@result,PATINDEX('%/%',@result))

    select replace(@result,left(@result,PATINDEX('%/%',@result)),'')

  • September 28, 2009 at 1:30 pm

    #1059180

    arun.sas (9/25/2009)

    declare @result varchar(20)

    select @result = 'testdb/sysdb'

    select PATINDEX('%/%',@result)

    select left(@result,PATINDEX('%/%',@result))

    select replace(@result,left(@result,PATINDEX('%/%',@result)),'')

    thanks !! i dont understand de meaning of this one ...

    "select @result = 'testdb/sysdb'"

  • September 28, 2009 at 2:28 pm

    #1059204

    what should be @result value...that confused me ...

  • September 28, 2009 at 2:49 pm

    #1059209

    Patindex finds the first place that the substring appears. It nests that in Left, which gets everything up to that point. Then it replaces that in the string.

    If you separately look up Patindex, Left, and Replace, it should end up making sense.

    However, I have to say that I see weaknesses in that solution, depending on your data.

    First, will there ever be more than one "/" in the string? If so, do you want to get rid of everything left of the first one, or everything left of the last one? (Further suggestions depend on the answer to that question.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 28, 2009 at 3:11 pm

    #1059216

    GSquared (9/28/2009)

    Patindex finds the first place that the substring appears. It nests that in Left, which gets everything up to that point. Then it replaces that in the string.

    If you separately look up Patindex, Left, and Replace, it should end up making sense.

    However, I have to say that I see weaknesses in that solution, depending on your data.

    First, will there ever be more than one "/" in the string? If so, do you want to get rid of everything left of the first one, or everything left of the last one? (Further suggestions depend on the answer to that question.)

    thanks!

    I understand, patindex, replace and left. The only thing i dont get is the value of @result presentend in de answer before. ¿wich value should it have?¿what it does?

    this time i have only one '/', in each data, so i guess it´ll be ok.

    thanks in advance for your help.

  • September 28, 2009 at 3:16 pm

    #1059217

    @result is just a variable being used for the sample code.

    You'd use your column name instead.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 28, 2009 at 3:29 pm

    #1059228

    GSquared (9/28/2009)

    @result is just a variable being used for the sample code.

    You'd use your column name instead.

    Thanks!

    So the procedure looks like ;

    PROCEDURE [dbo].[limpiar]

    AS

    BEGIN

    declare @result varchar(20)

    select @result = 'desprod'

    select PATINDEX('%/%',@result)

    select left(@result,PATINDEX('%/%',@result))

    select replace(@result,left(@result,PATINDEX('%/%',@result)),'')

    END

    i executed it and i got 3 result sets,

    1. 0 (no column name)

    2. null (no column name)

    3. desprod (no clumn name)

    Also it didn´t change anything 😀

    ¿¿ what´s wrong ??

  • September 29, 2009 at 7:00 am

    #1059434

    Is "desprod" the column you want to change?

    If so, try this first:

    select replace(desprod,left(desprod,PATINDEX('%/%',desprod)),'')

    from dbo.MyTable;

    You'll need to use the actual table name, where I have "MyTable".

    Does that give you a list of the values you want? If so, then we can modify it into an Update statement, if you want to actually change the data in the table.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 29, 2009 at 7:30 am

    #1059462

    GSquared (9/29/2009)

    Is "desprod" the column you want to change?

    If so, try this first:

    select replace(desprod,left(desprod,PATINDEX('%/%',desprod)),'')

    from dbo.MyTable;

    You'll need to use the actual table name, where I have "MyTable".

    Does that give you a list of the values you want? If so, then we can modify it into an Update statement, if you want to actually change the data in the table.

    thanks man !

    it does what it is supossed to.

    I´m gonna do the update statement and see what i get.

  • September 29, 2009 at 7:32 am

    #1059466

    Before you jump in and try this on a production database, test it first on a copy of the data that you can replace if it gets messed up.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 29, 2009 at 7:38 am

    #1059468

    GSquared (9/29/2009)

    Before you jump in and try this on a production database, test it first on a copy of the data that you can replace if it gets messed up.

    thanks for all the advices.

    i realice that i´ve never used replace or patindex, so my new question is.

    How do i update using the sentence that you give me before. ??

  • September 29, 2009 at 7:46 am

    #1059476

    igngua (9/29/2009)

    GSquared (9/29/2009)

    Before you jump in and try this on a production database, test it first on a copy of the data that you can replace if it gets messed up.

    thanks for all the advices.

    i realice that i´ve never used replace or patindex, so my new question is.

    How do i update using the sentence that you give me before. ??

    i feel like a hardcore noob....:-D

  • September 29, 2009 at 7:48 am

    #1059477

    igngua (9/29/2009)

    igngua (9/29/2009)

    GSquared (9/29/2009)

    Before you jump in and try this on a production database, test it first on a copy of the data that you can replace if it gets messed up.

    thanks for all the advices.

    i realice that i´ve never used replace or patindex, so my new question is.

    How do i update using the sentence that you give me before. ??

    i feel like a hardcore noob....:-D

    update softland.iw_tprod set desprod =

    replace(desprod,left(desprod,PATINDEX('%/%',desprod)),'')

    It´s ok??

  • September 29, 2009 at 7:54 am

    #1059481

    igngua (9/29/2009)

    igngua (9/29/2009)

    igngua (9/29/2009)

    GSquared (9/29/2009)

    Before you jump in and try this on a production database, test it first on a copy of the data that you can replace if it gets messed up.

    thanks for all the advices.

    i realice that i´ve never used replace or patindex, so my new question is.

    How do i update using the sentence that you give me before. ??

    i feel like a hardcore noob....:-D

    update softland.iw_tprod set desprod =

    replace(desprod,left(desprod,PATINDEX('%/%',desprod)),'')

    It´s ok??

    it worked!!

    thanks gsquared!!!

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

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