Post reply

i need the location of the nth pipe in a given string. Must be dynamic. N will be input param eventually

  • January 26, 2011 at 12:31 pm

    #230816

    declare @position int

    declare @string varchar(2000)

    set @string = 'American Baby|BabyTalk|CTU|CordTrust|Gerber Life|BOTM Club|Career_Institute|Fast Lane|FinancialMatch|Kabeep|Aricept_PD|CBOTMC_TIB|Arca_Recipe_RP|FCR|Columbia|TruGreen_Rex|CHJ_Ziploc_RetPath|Terminix_MedWhiz|ScienceFiction_PD|Cosmetique_Rex|PerfectDiet_rex|QuickRewards|WomensDebt|NewCar_Innov|Maxima_PD|MyWireless_PD|Encore|'

    set @position = 3

    --does anyone know a simple way to get the location of the third pipe?

    --in this instance it is the third position however it could be anything between 1 and n. So the code has to be dynamic that way.

    --output = 27

    set @position = 6

    --output should be 59

    set @position = 5

    --output should be 49

    --etc.

  • January 26, 2011 at 12:39 pm

    #1278723

    declare @position int

    declare @string varchar(2000)

    set @string = 'American Baby|BabyTalk|CTU|CordTrust|Gerber Life|BOTM Club|Career_Institute|Fast Lane|FinancialMatch|Kabeep|Aricept_PD|CBOTMC_TIB|Arca_Recipe_RP|FCR|Columbia|TruGreen_Rex|CHJ_Ziploc_RetPath|Terminix_MedWhiz|ScienceFiction_PD|Cosmetique_Rex|PerfectDiet_rex|QuickRewards|WomensDebt|NewCar_Innov|Maxima_PD|MyWireless_PD|Encore|'

    set @position = 5

    ;WITH StringBreaker(Pos, Ordinal)as

    (SELECT Number, ROW_NUMBER() OVER (ORDER BY Number)

    FROM dbo.Numbers

    WHERE SUBSTRING(@string, Number, 1) = '|')

    SELECT Pos

    FROM StringBreaker

    WHERE Ordinal = @position;

    Requires a Numbers table (Tally table to some people).

    - 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

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

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