SQL Column has a value I need to split into two columns

Question

For example

I have:

PN*BR        PN
872*07       872
8445*07      8455

I need a new BR column:

PN*BR        PN     BR
872*07       872    07
8445*07      8455   07

I can't see how this can be a copy of that specific question. They have very little in common. It may be a copy of other questions, but not that linked question — t-clausen.dk, Mar 15 '14 at 09:30
I agree with @t-clausen.dk this question is different because of the PN column -- it is not simply a string split. Gordon's answer shows that. — Hogan, Mar 15 '14 at 14:45

Gordon Linoff · Answer 1 · 2014-03-14T14:59:26.057

2

Here is one way:

select t.*,
       replace(replace([PN*BR], PN, ''), '*', '') as BR
from table t

Repeated values are a problem. And now that the question is tagged with a database, I would suggest:

select t.*,
       replace([PN*BR], PN + '*', '') as BR
from table t

edited Mar 14 '14 at 14:59

answered Mar 14 '14 at 13:31

Gordon Linoff

1,242,037
58
646
786

What about the PN column? – Hogan Mar 14 '14 at 13:37
+1 I agree with the reasoning – Giannis Paraskevopoulos Mar 14 '14 at 13:43
hopefully [PN*BR] will not contain values like '07*07' – t-clausen.dk Mar 14 '14 at 13:50
Hmmm seems the question changed – Hogan Mar 14 '14 at 15:05
@GordonLinoff - Actually it can. When you create a question or comment you have a few minutes "grace" period to make changes -- these changes don't show up on the history and (more importantly for my saving face) they don't generate an "update" message to the page so if you catch the original version you don't see the update without a hard refresh. SO has one of the best UIs around, but this is one feature that can cause some slight confusion. Sure is nice when you see that typo right after a submit. – Hogan Mar 15 '14 at 14:36
@Hogan . . . That is a good point, and I use it quite frequently. I am deleting all my comments about this. Including this one (I hope) in a few minutes. Cheers. – Gordon Linoff Mar 15 '14 at 14:37

score 1 · Answer 2 · answered Mar 14 '14 at 13:32

1

SELECT LEFT([PN*BR],PATINDEX('%*%',[PN*BR])-1) AS PN,
       RIGHT([PN*BR],LEN([PN*BR]) - PATINDEX('%*%',[PN*BR])) AS BR
FROM   Table

answered Mar 14 '14 at 13:32

Giannis Paraskevopoulos

18,261
1
49
69

score 0 · Answer 3 · answered Mar 14 '14 at 13:49

0

SELECT t.*,
       STUFF([PN*BR], 1, CHARINDEX('*', [PN*BR]), '') BR
FROM <yourtable> t

answered Mar 14 '14 at 13:49

t-clausen.dk

43,517
12
59
92

score 0 · Answer 4 · answered Mar 14 '14 at 14:16

0

SELECT SUBSTRING(PN*BR,1,CHARINDEX('*',PN*BR,1)-1) as PN, SUBSTRING(PN*BR,CHARINDEX('*',PN*BR,1)+1,LEN(PN*BR)-CHARINDEX('*',PN*BR,1)) as BR 
FROM Table

answered Mar 14 '14 at 14:16

Jonno Lord

314
1
3
14

score -1 · Answer 5 · edited May 23 '17 at 10:25

-1

Answered here: How to split a single column values to multiple column values?

Extract:

SELECT CASE
         WHEN name LIKE '% %' THEN LEFT(name, Charindex(' ', name) - 1)
         ELSE name
       END,
       CASE
         WHEN name LIKE '% %' THEN RIGHT(name, Charindex(' ', Reverse(name)) - 1)
       END
FROM   YourTable

edited May 23 '17 at 10:25

Community

1
1

answered Mar 14 '14 at 13:35

Shishir Kumar

7,981
3
29
45

there is no name column in the question and the question does not want to split on space. – Hogan Mar 14 '14 at 13:36
just trying to illustrate that the question has answer at the referred question and extracted the query from there. – Shishir Kumar Mar 14 '14 at 13:54

SQL Column has a value I need to split into two columns

5 Answers5