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