December 9, 2009 at 12:11 pm
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
December 9, 2009 at 12:26 pm
Hope this code helps:
select substring(YourColumn, 1, charindex('-',YourColumn) - 1), substring(YourColumn, charindex('-',YourColumn) + 1,len(YourColumn) - charindex('-',YourColumn))
December 9, 2009 at 12:26 pm
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
PLM 5000
Lowell
December 9, 2009 at 12:43 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply