Parse Substring a string sql

Question

I have a table with a column data as below:

MASTERDATA-8.009.9.9.1    
CHILDDATA-9.007.1.1.1      
MASTERDATA-2.003.1.1.2

I want my output as two columns :

COlumn1               Column2           
MASTERDATA.009        8.9.9.1     
CHILDDATA.007        9.1.1.1  
MASTERDATA.003       2.1.1.2

Sample Table Script:

Create Table ParseData
(
ColumnToParse Varchar(50)
)

Insert into ParseData values('MASTERDATA-8.009.9.9.1')
Insert into ParseData values('CHILDDATA-9.007.1.1.1')
Insert into ParseData values('MASTERDATA-2.003.1.1.2')

SqlZim · Accepted Answer · 2017-01-30 16:31:14Z

4

For simple parsing using charindex():

rextester: http://rextester.com/KYBOZ21026

select column1=left(ColumnToParse
                ,charindex('-',ColumnToParse )-1)
              +substring(ColumnToParse
                ,charindex('.',ColumnToParse,charindex('-',ColumnToParse )+1),4)
      ,column2=substring(ColumnToParse,charindex('-',ColumnToParse )+1,1)
                +right(ColumnToParse
                ,len(ColumnToParse )
                 -charindex('.',ColumnToParse,charindex('-',ColumnToParse )+1)-3
                    )
from parsedata

results in:

+----------------+---------+
|    column1     | column2 |
+----------------+---------+
| MASTERDATA.009 | 8.9.9.1 |
| CHILDDATA.007  | 9.1.1.1 |
| MASTERDATA.003 | 2.1.1.2 |
+----------------+---------+

edited Jan 30, 2017 at 16:31

answered Jan 30, 2017 at 16:03

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SuperKings Over a year ago

Thanks but i am looking for a different output. If My source COlumn value is MASTERDATA-8.009.9.9.1 ,Then my output should be MASTERDATA.009 and 8.9.9.1

John Cappelletti Over a year ago

Your close, but does not produce the desired output.. Just a tweak would be required

Pரதீப் Over a year ago

Just wondering how this answer got 3 upvotes though it does not produce the exact result ;)

SqlZim Over a year ago

@prdp good question, probably people making the same assumptions as I did without a double take at the question.

Pரதீப் Over a year ago

Between am not the downvoter ;) Upvoted now for the correct answer :)

Pரதீப் · Accepted Answer · 2017-01-30 16:28:52Z

1

Considering all the records are in similar format. Here is one way using string functions

SELECT column1 = LEFT(ColumnToParse, Charindex('-', ColumnToParse) - 1)
                 + Substring(ColumnToParse, Charindex('.', ColumnToParse), Charindex('.', ColumnToParse, Charindex('.', ColumnToParse)+1 ) - Charindex('.', ColumnToParse) ),
       column2 = Substring(ColumnToParse, Charindex('-', ColumnToParse)+1, Charindex('.', ColumnToParse) - Charindex('-', ColumnToParse) )
                 + RIGHT(ColumnToParse, Len(ColumnToParse)- Charindex('.', ColumnToParse, Charindex('.', ColumnToParse)+1 ))
FROM   ParseData

Live Demo

edited Jan 30, 2017 at 16:28

answered Jan 30, 2017 at 16:23

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

cloudsafe · Accepted Answer · 2017-01-30 16:37:12Z

0

select left(ColumnToParse, CHARINDEX('-', ColumnToParse) ) + 
    substring(columntoparse, CHARINDEX('-', ColumnToParse)+1,5) as Column1
    , right(replace(columntoparse,substring(columntoparse, 
    CHARINDEX('-', ColumnToParse)+2,4),''),7) as Column2
from ParseData

edited Jan 30, 2017 at 16:37

answered Jan 30, 2017 at 16:18

cloudsafe

2,5241 gold badge11 silver badges27 bronze badges

Collectives™ on Stack Overflow

Parse Substring a string sql

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related