Return specific part of string

Question

Problem: Current input in numeric#; varchar

eg: 404#;a pruchase order

1#; b purchase order

1046#;x y x purchase order from this company

I need to have the numbers at the beginning put in a column and the name of the field after #; in a different column.

Parse will not work because there is a possibility of greater than 4 words in the title

As you can see #; is a common feature in all the inputs.

What I would like to see happen is:

ID           Name
404          a purchase order
1            b purchase order
1046         xyz purchase order from this company

Any ideas?

I tried How do I split a string so I can access item x?

but that wouldn't work for me

What version of SQL Server? – TMNT2014 Aug 21 '14 at 15:21 — TMNT2014, Aug 21 '14 at 15:21

score 3 · Accepted Answer · edited Aug 21 '14 at 15:22

3

declare @a varchar(20)
set @a = '123#;gunrin gnre'

SELECT 
    SUBSTRING(@a,0,CHARINDEX('#',@a)) AS ID
    , SUBSTRING(@a,CHARINDEX('#',@a)+2,LEN(@a)-CHARINDEX('#',@a)) AS Name

edited Aug 21 '14 at 15:22

T I

9,785
4
29
51

answered Aug 21 '14 at 15:21

JoseTeixeira

1,296
8
14

score 1 · Answer 2 · answered Aug 21 '14 at 15:25

1

Declare @Sample NVARCHAR(100)

 SET @Sample = '1046#;x y x purchase order from this company'

SELECT SUBSTRING(@Sample, 1, CHARINDEX('#;', @Sample)-1), SUBSTRING(@Sample, CHARINDEX('#;', @Sample)+2,LEN(@Sample) )

answered Aug 21 '14 at 15:25

TMNT2014

2,102
1
11
13

Piotr Miś · Answer 3 · 2014-08-21T15:43:01.670

What about:

with Test as (
    select * from (values 
        ('404#;a purchase order'),
        ('1#;b purchase order'),
        ('1046#;xyz purchase order from this company'),
        ('29323982#;evil test 231#;')
    ) tmp(str)        
)
select cast(left(str, idx-1) as int) as ID,
       right(str, len(str)-idx-1)    as Name
from Test t
cross apply (select charindex('#;', t.str) as idx) tmp

-- ID          Name
-- ----------- ------------------------------------------
-- 404         a purchase order
-- 1           b purchase order
-- 1046        xyz purchase order from this company
-- 29323982    evil test 231#;
-- 
-- (4 row(s) affected)

Keep in mind though, that cross apply is MSSQL-specific.

Return specific part of string

3 Answers3