Splitting one column into 2 columns - SQL Server

Question

I have 2 pieces of data

     Column1
DY04837293530000LFOY
DY9586074510000LOY

I am looking for this result

 Column1           Column2
DY04837293         530000
DY95860745         10000

I need to take the first 10 characters and put them in the first column and then remove the characters on the end and just add the remaining numbers in the middle to column 2.

Can anyone give me any advice on the best way to approach this? thanks

https://learn.microsoft.com/pt-br/sql/t-sql/functions/substring-transact-sql — lmarqs, Feb 23 '18 at 14:29
Did you check this https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — Maytham Fahmi, Feb 23 '18 at 14:30
If you follow the link from @maytham-ɯɐɥʇʎɐɯ I would strongly suggest you run away from that loop based splitter in the accepted answer. Looping is a terrible way to split strings. But in your case you don't need a splitter because your string isn't delimited. — Sean Lange, Feb 23 '18 at 14:33
but please look at substring() left() and right() and usage of variables — MichaelEvanchik, Feb 23 '18 at 14:34

score 3 · Accepted Answer · answered Feb 23 '18 at 14:34

3

DECLARE @test VARCHAR(255) = 'DY04837293530000LFOY'

SELECT  @test ori
,       SUBSTRING(@test, 1, 10) first_10
,       SUBSTRING(@test, 11, PATINDEX('%[^0-9]%', SUBSTRING(@test, 11, LEN(@test))) - 1) last_numbers

Results in:

+----------------------+------------+--------------+
|         ori          |  first_10  | last_numbers |
+----------------------+------------+--------------+
| DY04837293530000LFOY | DY04837293 |       530000 |
+----------------------+------------+--------------+

answered Feb 23 '18 at 14:34

HoneyBadger

14,750
3
34
48

3

you could have let him at least try – MichaelEvanchik Feb 23 '18 at 14:35
@MichaelEvanchik I know, that's usually my sentiment as well. This time I decided to answer. – HoneyBadger Feb 23 '18 at 14:37

Splitting one column into 2 columns - SQL Server

1 Answers1