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
– TMNT2014

2014-08-21 15:21:50 +00:00
Commented Aug 21, 2014 at 15:21 — TMNT2014
– TMNT2014, Commented Aug 21, 2014 at 15:21

T I · Accepted Answer · 2014-08-21 15:22:26Z

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, 2014 at 15:22

T I

9,9634 gold badges31 silver badges51 bronze badges

answered Aug 21, 2014 at 15:21

JoseTeixeira

1,2968 silver badges15 bronze badges

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

Comments

TMNT2014 · Accepted Answer · 2014-08-21 15:25:29Z

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, 2014 at 15:25

TMNT2014

2,1401 gold badge12 silver badges13 bronze badges

Comments

Piotr Miś · Accepted Answer · 2014-08-21 15:43:01Z

0

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.

edited Aug 21, 2014 at 15:43

answered Aug 21, 2014 at 15:34

Piotr Miś

9815 silver badges6 bronze badges

Collectives™ on Stack Overflow

Return specific part of string

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related