substring with Charindex

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  January 9, 2019 at 7:21 am

  #2018417

  drew.allen - Tuesday, January 8, 2019 2:06 PM

  Michael L John - Tuesday, January 8, 2019 1:14 PM

  ettentrala - Tuesday, January 8, 2019 12:16 PM

  My apologizes, I was wrong,

  Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64)   Oct  3 2017 14:56:10   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

  Well, Jeff Moden's string splitter is probably your best bet!

  
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
SELECT *
FROM [dbo].[DelimitedSplit8K] (@string, '.')


  Results:

  ItemNumber	ItemValue
  1	Cum CA:12
  2	0 CE:12
  3	0 QP:42
  4	00 QPA:3
  5	500

  There has been an update to the splitter that uses LEAD() and is about 50% faster IIRC.  That is your best alternative.  I believe that there is a link to the update in Jeff's article.

  Drew

  The improvement you speak of is buried in an article titled in a totally unrelated fashion.  It was written by Eirikur Eiriksson and, yes... I tested it before he submitted the article.  It is literally twice as fast thanks to the use of the "new" LEAD functionality, which became available in 2012, to replace the use of CHARINDEX() to find the "next" delimiter.

  Here's the link to Eirikur's article....
  http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

  I should add another update to my article to make Eirikur's wonderful modification easier to find.

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

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  January 9, 2019 at 8:06 am

  #2018425

  Jeffrey Williams 3188 - Tuesday, January 8, 2019 3:08 PM

  If the values in the string can be in any order it gets much more difficult using a traditional string splitter. Using the XML splitter method would be easier:

  And, usually, slower. 😉

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