asked on

Trim data and separate into two

hi every body.

i have the ff: table .

input table:
title Volume issue
Supersonic 1-2 1-2

output table:
title Volume Volstart VolEnd IssStart IssEnd
Supersonic 1-2 1 2 1 2

I want to populate the volume data before the dash as Volstart and the data after the dash as volend, same goes with Issue field. though if the data has no '-' (dash), the query s/b able to populate same data to VolStart and VolEnd. How should i update the table in a single query?

Muhammad Ahmad Imran

select volume,
substring(volume,1,CHARINDEX('-',volume)),
substring(volume,CHARINDEX('-',volume)+1,len(volume)-charindex('-',volume),
issue,
substring(issue,1,CHARINDEX('-',issue)),
substring(issue,CHARINDEX('-',issue)+1,len(issue)-charindex('-',issue)
from your_table

hope this help

Muhammad Ahmad Imran

Ooooops .. a little mistake

select volume,
substring(volume,1,CHARINDEX('-',volume)),
substring(volume,CHARINDEX('-',volume)+1,len(volume)-charindex('-',volume)),
issue,
substring(issue,1,CHARINDEX('-',issue)),
substring(issue,CHARINDEX('-',issue)+1,len(issue)-charindex('-',issue))
from your_table

gladxml

ASKER

i got the ff: error

<<Error in list of function arguments: 'FROM' not recognized.
Unable to parse query text.>>

gladxml

ASKER

i got the ff: result from ur 2nd post.
Volume Volstart VolEnd Issue issStart issEnd
1 1 1-2 1- 2

thanks for ur patience

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julianva

You can use substring

do this first to see if you get the correct results then use the update query

select substring(volume,1,1) from tablename

if you get the correct results then update

update table

set volstart = substring(Volume,1,1),
volend = substring(volume,2,3)

Hilaire

Please try

select Title, Volume,
coalesce(parsename(replace(Volume, '-', '.'),2), parsename(replace(Volume, '-', '.'),1)) as VolStart,
coalesce(parsename(replace(Volume, '-', '.'),1), parsename(replace(Volume, '-', '.'),2)) as VolEnd,
Issue,
coalesce(parsename(replace(Issue, '-', '.'),2), parsename(replace(Issue, '-', '.'),1)) as IssStart,
coalesce(parsename(replace(Issue, '-', '.'),1), parsename(replace(Issue, '-', '.'),2)) as IssEnd
from YourTable