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

• BaldingLoopMan

  SSCertifiable

  Points: 6378

  More actions

  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.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  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