?
Solved

Trim data and separate into two

Posted on 2005-04-27
7
Medium Priority
?
306 Views
Last Modified: 2010-03-19
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?




0
Comment
Question by:gladxml
7 Comments
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 13881788
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
0
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 13881795
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
0
 
LVL 15

Author Comment

by:gladxml
ID: 13881828
i got the ff: error

<<Error in list of function arguments: 'FROM' not recognized.
Unable to parse query text.>>
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Author Comment

by:gladxml
ID: 13881840
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
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13882426
Try this:

select volume,
LEFT(volume, isnull(nullif(charindex('-', volume) - 1, -1), len(volume))),
RIGHT(volume, isnull(nullif(charindex('-', reverse(volume)) - 1, -1), len(volume))),
issue,
LEFT(issue, isnull(nullif(charindex('-', issue) - 1, -1), len(issue))),
RIGHT(issue, isnull(nullif(charindex('-', reverse(issue)) - 1, -1), len(issue)))
0
 
LVL 8

Expert Comment

by:Julianva
ID: 13883360
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)





0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13883989
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

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question