Select from String

• Terence Keys

  Ten Centuries

  Points: 1090

  More actions

  January 14, 2009 at 5:22 am

  #73180

  I need to select only a part of a text string....

  I have the following 3 rows...

  Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId

  Provider PraNum="3804917" Funder DisplayName="Second" emMA="28223" Industry RefId0901062356255634nv4nkt RefId

  Provider PraNum="3802078" Funder DisplayName="BEST" MemMA="41807" Industry RefId 090107002846839na1e62m RefId

  I need to SELECT everything between the two words 'Funder' & 'Industry'

  IE my results must be:

  DisplayName="Health" MemMAPlan="41807"/> </

  DisplayName="Second" MemMA="28223"/> </

  DisplayName="BEST" MemMA="41807"/> </

  Any help will be greatly appreciated...

  Thanks

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  January 14, 2009 at 5:31 am

  #926485

  You can make use of String function CHARINDEX along with SUBSTRING

  -Vikas Bindra

• Terence Keys

  Ten Centuries

  Points: 1090

  More actions

  January 14, 2009 at 5:35 am

  #926489

  This is what I did but it returns more than what I need...

  SELECT SUBSTRING(routingheader, charindex('funder',routingheader), charindex('funder',routingheader)) AS ROUTINGHEADER

• Terence Keys

  Ten Centuries

  Points: 1090

  More actions

  January 14, 2009 at 5:39 am

  #926494

  This is what I used but it returns everything AFTER the word Industry as well..

  SELECT SUBSTRING(routingheader, charindex('funder',routingheader), charindex('industry',routingheader)) AS ROUTINGHEADER

  All I need is what is between the two words: funder and industry

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 14, 2009 at 6:11 am

  #926508

  Just a little modification in the query....

  Note:

  This code assumes that there exists values "Funder" & "Industry" in the column. Otherwise, you have to manually handle those exceptions by filtering it out.

  SELECTCHARINDEX( 'Funder', routingheader ),

  CHARINDEX( 'Industry', routingheader ),

  SUBSTRING( routingheader, CHARINDEX( 'Funder', routingheader ) + LEN( 'Funder' ),

  CHARINDEX( 'Industry', routingheader ) - CHARINDEX( 'Funder', routingheader )

  - LEN( 'Funder' ) ) AS ROUTINGHEADER

  FROM

  (

  SELECT 'Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId' AS routingheader

  UNION ALL

  SELECT 'Provider PraNum="3804917" Funder DisplayName="Second" emMA="28223" Industry RefId0901062356255634nv4nkt RefId' AS routingheader

  UNION ALL

  SELECT 'Provider PraNum="3802078" Funder DisplayName="BEST" MemMA="41807" Industry RefId 090107002846839na1e62m RefId' AS routingheader

  ) R

  --Ramesh
  

  ---

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 14, 2009 at 6:20 am

  #926513

  Terence Keys (1/14/2009)

  ---

  This is what I used but it returns everything AFTER the word Industry as well..

  SELECT SUBSTRING(routingheader, charindex('funder',routingheader), charindex('industry',routingheader)) AS ROUTINGHEADER

  All I need is what is between the two words: funder and industry

  Good start, Terence, but it's a little more complex than that. Run the following piece of code and you will see why.

  DECLARE @routingheader VARCHAR(200)

  SET @routingheader = 'Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId'

  SELECT @routingheader,

  MatchValue = SUBSTRING(@routingheader, 34, 38),

  Startpos = charindex('funder',@routingheader)+LEN('funder ')+1,

  Length = charindex('industry',@routingheader) - (charindex('funder',@routingheader)+LEN('funder ')+2),

  SUBSTRING(@routingheader, charindex('funder',@routingheader)+LEN('funder ')+1,

  charindex('industry',@routingheader) - (charindex('funder',@routingheader)+LEN('funder ')+2))

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Terence Keys

  Ten Centuries

  Points: 1090

  More actions

  January 14, 2009 at 9:22 pm

  #927050

  Thanks for the help!

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  January 15, 2009 at 7:22 am

  #927284

  SELECT CHARINDEX( 'Funder', routingheader ),

  CHARINDEX( 'Industry', routingheader ),

  SUBSTRING( routingheader, CHARINDEX( 'Funder', routingheader ) + LEN( 'Funder' ),

  CHARINDEX( 'Industry', routingheader ) - CHARINDEX( 'Funder', routingheader )

  - LEN( 'Funder' ) ) AS ROUTINGHEADER

  FROM

  (

  SELECT 'Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId' AS routingheader

  UNION ALL

  SELECT 'Provider PraNum="3804917" Funder DisplayName="Second" emMA="28223" Industry RefId0901062356255634nv4nkt RefId' AS routingheader

  UNION ALL

  SELECT 'Provider PraNum="3802078" Funder DisplayName="BEST" MemMA="41807" Industry RefId 090107002846839na1e62m RefId' AS routingheader

  ) R

  A little modification in Ramesh's code...

  SELECT CHARINDEX( 'Funder', routingheader ),

  CHARINDEX( 'Industry', routingheader ),

  SUBSTRING( routingheader, CHARINDEX( 'DisplayName', routingheader)

  CHARINDEX( 'Industry', routingheader ) - CHARINDEX( 'Funder', routingheader )

  - LEN( 'Funder' ) ) AS ROUTINGHEADER

  karthik

• Jeff Moden

  SSC Guru

  Points: 1003871

  More actions

  January 15, 2009 at 8:29 am

  #927380

  Terence Keys (1/14/2009)

  ---

  Thanks for the help!

  So, what did your final code end up looking like?

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

• Terence Keys

  Ten Centuries

  Points: 1090

  More actions

  January 15, 2009 at 11:11 pm

  #927846

  Thanks for all the help!! you pointed

  me in the right direction with the substring and charindex..

  The following code worked for me...

  DECLARE eRA_Report CURSOR FOR

  SELECT originalmessageid

  FROM AM

  WHERE Messagetype = 'Normal/Remittanceadv' AND RecipientAddress LIKE '%PRO027%' AND Timestamp BETWEEN @StartDate AND @EndDate

  --and originalmessageid in ('090112123227693mec1xtk')

  OPEN eRA_Report

  FETCH NEXT FROM eRA_Report INTO @originalmessageid

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @substring_input_2 = (SELECT CHARINDEX('DisplayName="', routingheader) from am where originalmessageid = @originalmessageid)

  SET @substring_input_3 = (select CHARINDEX(' ', routingheader) from am where originalmessageid = @originalmessageid)

  SET @substring_input_3 = @substring_input_3 - @substring_input_2 + 9

  SET @substring_input_2 = @substring_input_2 - 9

  SET @routingheader = (select substring(routingheader,@substring_input_2,@substring_input_3) from am where originalmessageid = @originalmessageid)

  SET @substring_input_3 = null

  SET @substring_input_2 = null

  INSERT INTO eRA_TEMP_Table (ORIGINALMESSAGEID, REFERENCEMESSAGEID, TIMESTAMP, MESSAGETYPE, SENDER, RECIPIENTADDRESS, OBO, QUANTITY, TRANSACTIONNUMBER, PRIORITY, COMPRESSION, SECURITY,RoutingHeader)

  SELECT ORIGINALMESSAGEID, REFERENCEMESSAGEID, TIMESTAMP, MESSAGETYPE, SENDER, RECIPIENTADDRESS, OBO, QUANTITY, TRANSACTIONNUMBER, PRIORITY, COMPRESSION, SECURITY, @RoutingHeader as 'RoutingHeader'

  FROM AM

  WHERE originalmessageid = @originalmessageid

  SET @routingheader = null

  FETCH NEXT FROM eRA_Report INTO @originalmessageid

  END

• Jeff Moden

  SSC Guru

  Points: 1003871

  More actions

  January 15, 2009 at 11:22 pm

  #927854

  Oh dear... thanks for sharing, but why on Earth did you use a cursor? That's gonna slow it down, alot!

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

• Terence Keys

  Ten Centuries

  Points: 1090

  More actions

  January 16, 2009 at 1:06 am

  #927882

  It does run longer yes.. but it's a very small db and the report only runs once a week..

  Thanks again for the help!!