lundi 4 novembre 2019

SQL Server split sting based on patterns

I'm trying to split a specific string based on two patterns for the first an last character, the first character must be any numeric value and the last is a comma.

I'm using the patindex, substring and charindex functions but they only give a single row.

This is the query that I am working on:

DECLARE @x varchar(max) = 'In the vocational certificate category, if served as laundry/dry cleaning specialist, 2 semester hours in treatment of fabrics (stain removing, spotting, solutions), 2 in laundry equipment operation, 2 in dry cleaning, 3 in small business management, 3 in introduction to business, 3 in supply management, and 3 in business report writing.'
   Declare @extract varchar(max) = ''
   set @extract = substring(@x, patindex('%[0-99] [i]%[,] %', @x), (LEN(@x)) -  CHARINDEX('%[0-99] [i]%[,] %', REVERSE(@x))) 
   select @extract 

The result is this: 2 in laundry equipment operation, 2 in dry cleaning, 3 in small business management, 3 in introduction to business, 3 in supply management, and 3 in business report writing.

Is there any way to get the results in columns? Like this:

  • 2 in laundry equipment operation
  • 2 in dry cleaning
  • 3 in small business management

...

Aucun commentaire:

Enregistrer un commentaire