CODEWITHSUNDEEP
sql-server
Chris
Chris

Reputation: 33

String Split using SQL

I am using MS SQL Server Management Studio v17.

I have a string:

Primary sector: General Industries Group Sub-sector: Chemicals (incl. agri businesses) Tier:6

Expected Output

Primary_Sector
General Industries Group

Sub_Sector
Chemicals

I want to compute two new variables - Primary_Sector and Sub_Sector that contains General Industries Group and Chemicals respectively. Do you know what would be the best technique to extrapolate the text and how I would do it?

Upvotes: 1

Views: 94

Answers (2)

Sreenu131
Sreenu131

Reputation: 2516

Try this

DECLARE @Tabl AS TABLE(data nvarchar(max))
INSERT INTO @Tabl
SELECT 'Primary sector: General Industries Group Sub-sector: Chemicals (incl. agri businesses) Tier:6'

SELECT  data,
        [Primary sector],
        SUBSTRING([Sub-sector],1,CHARINDEX(')',[Sub-sector]))AS [Sub-sector],
        Tier 
FROM
(
SELECT data,
        REPLACE(SUBSTRING(data,0,CHARINDEX('Sub-sector:',data)),':',' = ') AS [Primary sector],
        REPLACE(SUBSTRING(data,CHARINDEX('Sub-sector:',data),LEN(data)),':',' = ') AS [Sub-sector]
        ,RIGHt(data,LEn('Tier:')+1) AS [Tier]
 FROM @Tabl
 )dt

Demo Result:http://rextester.com/ARPH77432

Upvotes: 0

Tyron78
Tyron78

Reputation: 4187

I think it's pretty simple via Substring:

DECLARE @x NVARCHAR(100) = 'Primary sector: General Industries Group Sub-sector: Chemicals (incl. agri businesses) Tier:6';

DECLARE @l INT = LEN(@x);
DECLARE @iTier INT = CHARINDEX('Tier:', @x)
DECLARE @iSubSector INT = CHARINDEX('Sub-sector:', @x)
DECLARE @iPrimarySector INT = CHARINDEX('Primary sector:', @x)

SELECT 'Tier = ' +  SUBSTRING(@x, @iTier+5, @l-(@iTier+5)+1)
SELECT 'SubSector = ' + SUBSTRING(@x, @iSubSector+11, @iTier-(@iSubSector+11))
SELECT 'PrimarySector = ' + SUBSTRING(@x, @iPrimarySector+15, @iSubSector-(@iPrimarySector+15))

Upvotes: 1

Related Questions