Parsename - Replace - Strings

• Ganners

  SSChasing Mays

  Points: 636

  More actions

  February 9, 2016 at 4:03 am

  #308303

  Luis Cazares and Phil Parkin kindly helped me with the first problem.

  Problem 2 and 3 I need help with.

  I am having problems Parsing strings.

  Problem 1 has been resolved.

  Problem 1

  688||2||200501042350

  805||1||200506171400

  1342||6||200408260230

  2168||3||200603300248

  2168||3||200603301928

  2487||4||200502211506

  Needed Result

  688-2

  805-1

  1342-6

  2168-3

  2168-3

  2487-4

  2487-8

  Solved With 2 pipes

  ,PARSENAME( REPLACE(string, '||', '.'), 3) + '-' + PARSENAME( REPLACE(string, '||', '.'), 2)

  Problem 2 - 3 pipes

  601145||10||201008161530||1

  601145||12||201109081406||1

  601145||12||201109081406||2

  601145||13||201111040846||1

  601145||14||201205312225||1

  601145||14||201205312225||2

  601145||14||201205312225||3

  601145||14||201205312225||4

  601145||16||201304011255||1

  601145||17||201312121727||1

  601145||18||201501111505||1

  601145||18||201501111505||2

  601146||1||201507250720||1

  601146||1||201507250720||2

  601146||3||201510261300||1

  601147||18||201104111904||1

  601147||18||201104111904||2

  Need Result

  601145-10

  601145-12

  601145-12

  601145-13

  601145-14

  601145-14

  601145-14

  601145-14

  601145-16

  601145-17

  601145-18

  601145-18

  601146-1

  601146-1

  601146-3

  601147-18

  601147-18

  Problem 3 - 4 pipes

  aaa||aaa||201307082236||364797||1

  aaa||aaa||201307082248||853909||3

  aaa||aaaa||201307082249||344764||5

  aaa||aaaa||201307082254||836523||8

  aaa||aaaa||201307082255||303700||23

  aaa||aaaa||201307082314||91660||14

  aaa||aaaa||201307082323||161223||49

  aaa||aaaa||201307082345||466237||23

  Needed Result

  364797-1

  853909-3

  344764-5

  836523-8

  303700-23

  91660-14

  161223-49

  466237-23

  Thanks for any help.

• Phil Parkin

  SSC Guru

  Points: 246974

  More actions

  February 9, 2016 at 6:19 am

  #1857352

  First question: do you need to solve all of the problems in the same piece of SQL, or should they be addressed separately?

  It's probably easiest if you change your tack at this point. The PARSENAME() function is not useful when the number of items to split exceeds 4 (as in problem 3).

  It's easy enough to adapt one of Luis' solutions to solve the problems individually. Here is problem 3, for example:

  IF object_id('tempdb..#Sample') IS NOT NULL

  DROP TABLE #Sample

  CREATE TABLE #Sample ( Somestring varchar(250) );

  INSERT INTO #Sample

  VALUES ( 'aaa||aaaa||201307082323||161223||49' )

  , ( 'aaa||aaa||201307082236||364797||1' );

  SELECT MAX(CASE WHEN ItemNumber = 7 THEN Item END) + '-' + MAX(CASE WHEN ItemNumber = 9 THEN Item END)

  FROM #Sample

  CROSS APPLY dbo.DelimitedSplit8K( Somestring, '|')

  WHERE ItemNumber IN(7,9)

  GROUP BY Somestring;

  I'm sure that you can solve problem 2 yourself, by playing around with the ItemNumber selections.

  If you need to do this in one piece of SQL, however, it becomes somewhat more involved.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Ganners

  SSChasing Mays

  Points: 636

  More actions

  February 9, 2016 at 6:25 am

  #1857356

  Thanks for your help.

  Problem 2

  SELECT PARSENAME(REPLACE(Somestring, '||', '.'), 4) + '-' + PARSENAME( REPLACE(Somestring, '||', '.'), 3)

  Separate SQL will do.

  Thanks.