How to split a string into two different substring

Question

How to split a string into two different substring

sk.panda

Right there with Babe

Points: 796
More actions
December 9, 2009 at 12:11 pm

#73412

Dear All,
I have a situation where i need to split an SOC_MAPPING COLUMN with "-" in SQL Server 2000.
Lets say i want to split "PLM-5000" into two strings "PLM" and "5000".
For Example
SELECT SOC_MAPPING FROM INBOUND_PLM_CHCM
SOC_MAPPING
PLM-5000
PLM-5001
PLM-5002
PLM-5000
PLM-5000
After changing the query the OUTPUT SHOULD BE LIKE
SELECT STRING 1,STRING 2 FROM INBOUND_PLM_CHCM
STRING 1 STRING 2
PLM 5000
PLMR 5001
PLM 5002
PLM 5000
PLMS 50004
Please help me in this query.
Thanks a lot in advance.
Regards,
Sumanta Panda

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Hope this code helps:

select substring(YourColumn, 1, charindex('-',YourColumn) - 1), substring(YourColumn, charindex('-',YourColumn) + 1,len(YourColumn) - charindex('-',YourColumn))

Lowell SSC Guru Points: 323518 More actions · Answer 2

the trick is to use the CHARINDEX function to find the dash....potentially it could be in a different place than your example

this might get you started:

create table #example(SOC_MAPPING varchar(1200) )

insert into #example(SOC_MAPPING)

SELECT 'PLM-5000' UNION ALL

SELECT 'PLM-5001' UNION ALL

SELECT 'PLM-5002' UNION ALL

SELECT 'PLM-5000' UNION ALL

SELECT 'PLM-5000'

SELECT SUBSTRING(SOC_MAPPING,1,CHARINDEX('-',SOC_MAPPING) -1) AS LEFTHALF,--minus one to remove the dash

SUBSTRING(SOC_MAPPING,CHARINDEX('-',SOC_MAPPING) +1 ,100) AS RIGHTHALF --plus one to remove the dash

FROM #example

select CHARINDEX('-',SOC_MAPPING) from #example

--results:

LEFTHALFRIGHTHALF

PLM 5000

PLM 5001

PLM 5002

PLM 5000

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sk.panda Right there with Babe Points: 796 More actions · Answer 3

Thanks everyone for your prompt response.

The solution which you have provided to me is working fine.

Once again thanks for your valuable time.

Regards,

Sumanta