Parsing out a Data String with Text Delimeters

• evan.gorsky

  Newbie

  Points: 7

  More actions

  November 5, 2014 at 2:22 pm

  #312698

  Hi All,

  I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.

  For example,

  [atc1.123/atc2.456/atc3.789/atc4.xyz/]

  If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?

  Thank you so much for your help.

  -Evan

• Alan Burstein

  SSC Guru

  Points: 61152

  More actions

  November 5, 2014 at 3:43 pm

  #1756835

  Here's a function that should do the trick:

  CREATE FUNCTION dbo.SubstringBetween

  (

  @string varchar(1000),

  @searchString1 varchar(20),

  @searchString2 varchar(20)

  )

  /*

  -- example:

  DECLARE

  @string varchar(100) = '[atc1.123/atc2.456/atc3.789/atc4.xyz/]',

  @searchString1 varchar(20) = 'atc2',

  @searchString2 varchar(20) = 'atc3';

  SELECT newstring

  FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);

  */

  RETURNS TABLE AS

  RETURN

  SELECT newstring = SUBSTRING

  (

  @string,

  CHARINDEX(@searchString1, @string)+len(@searchstring1),

  CHARINDEX(@searchString2, @string)-CHARINDEX(@searchString1, @string)-(len(@searchstring1))

  );

  GO

  Example of how to use is in the comment portion of the code.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 5, 2014 at 5:54 pm

  #1756852

  Here are some examples on how to do it with the DelimitedSplit8k. The function code and explanation can be found in the following article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  SELECT LEFT(Item, CHARINDEX('.', Item)),

  STUFF( Item, 1, CHARINDEX('.', Item), ''),

  RIGHT( Item, LEN(Item) - CHARINDEX('.', Item)),

  PARSENAME(REPLACE(Item, '[', ''), 2),

  PARSENAME(REPLACE(Item, '[', ''), 1)

  FROM dbo.DelimitedSplit8K('[atc1.123/atc2.456/atc3.789/atc4.xyz/]', '/') s

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2