CODEWITHSUNDEEP
sql-servert-sql
user3311323
user3311323

Reputation:

Split values into seperate columns by '/'

I am selecting BP(sys/dia) from a table. I want the values split into two separate columns.

CREATE TABLE  #BP_INFO
(ID INT , NAME VARCHAR(10), BP VARCHAR(10))

INSERT INTO #BP_INFO
VALUES(1,'ABC','100/60')
     ,(2,'XYZ','120/70')
     ,(3,'GHD','110/55')
SELECT * FROM #BP_INFO

I would like the result shown below:

ID  NAME   BP_SYS   BP_DIA
1   ABC     100      60
2   XYZ     120      70
3   GHD     110      55

Upvotes: 0

Views: 284

Answers (3)

mohan111
mohan111

Reputation: 8865

we can proceed like this also 

CREATE TABLE  #BP_INFO
(ID INT , NAME VARCHAR(10), BP VARCHAR(10))

INSERT INTO #BP_INFO
VALUES(1,'ABC','100/60')
     ,(2,'XYZ','120/70')
     ,(3,'GHD','110/55')


SELECT ID,name,
SUBSTRING(BP,0,CHARINDEX('/',BP)) AS BP_SYS,
SUBSTRING(BP,CHARINDEX('/',BP)+1,LEN(BP))AS BP_DIA
 FROM #BP_INFO

Upvotes: 0

Vasily
Vasily

Reputation: 5782

additional variant to already posted by @JohnnyBell

SELECT  ID ,
        NAME ,
        SUBSTRING(BP, 1, CHARINDEX('/', BP) - 1) AS BP_SYS,
        SUBSTRING(BP, CHARINDEX('/', BP) + 1, 3) AS BP_DIA
FROM    #BP_INFO

Upvotes: 0

John Bell
John Bell

Reputation: 2350

That's some terrible mark up right there, but nevertheless, I got it.

SELECT ID, NAME
, LEFT(BP,CHARINDEX('/',BP)-1) AS BP_SYS
, RIGHT(BP,CHARINDEX('/',REVERSE(BP))-1) AS BP_DIA
FROM #BP_INFO

Another way, if you're like me, and like multiple ways of doing stuff:

SELECT ID, NAME
, PARSENAME(REPLACE(BP,'/','.'),2) AS BP_SYS
, PARSENAME(REPLACE(BP,'/','.'),1) AS BP_DIA
FROM #BP_INFO

Thank you whoever edited the OP. Much better.

Upvotes: 2

Related Questions