Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

update query to split field

Posted on 2008-06-13
7
212 Views
Last Modified: 2010-04-21
I have combined values in a field that I would like to split. Field1 has values like
     aaaaa
     aaaaa-111
     aaa-111-eee

I need to split off everything from the hypen over and add it to field2. I'm not sure how to set the criteria to include only those records with the "-" and how to isolate and move the values to the right of the hypen.

What is the best way to go about this?
0
Comment
Question by:eliwil
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 21780056
try this:
select Substring(FieldToFind, 0, CharIndex('-', FieldToFind)) as LeftOfDash ,
Substring(FieldToFind, CharIndex('-', FieldToFind), LEN(FieldToFind))  as RightOfDash,
Substring(FieldToFind, CharIndex('-', FieldToFind) + 1, LEN(FieldToFind))  as RightOfDashNotINcludingFirstDash
 
From TableName
where CharIndex('-', FieldToFind)  > 0

Open in new window

0
 
LVL 16

Expert Comment

by:brad2575
ID: 21780100
This gets you the fields but does not do the update.  Do you need the query to do that?  Or can you get that from the above?
0
 

Author Comment

by:eliwil
ID: 21780167
I'm getting a syntax error. Could you give me the complete statement? the table is TestResults and the field is Lot.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 16

Expert Comment

by:brad2575
ID: 21780262
What Version and type of SQL are you using?  MYSQL, MSSQL?
select Substring(LOT, 0, CharIndex('-', LOT)) as LeftOfDash ,
Substring(LOT, CharIndex('-', LOT), LEN(LOT))  as RightOfDash,
Substring(LOT, CharIndex('-', LOT) + 1, LEN(LOT))  as RightOfDashNotINcludingFirstDash
 
From TestResults 
where CharIndex('-', LOT)  > 0

Open in new window

0
 

Author Comment

by:eliwil
ID: 21780339
Sorry, I thought the question was posted to Access SQL rather than just SQL syntax. Should have double checked. Appreciate your assistance.
0
 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
ID: 21780361
Did this work for you?  I wrote this in MSSQL query but this "should" work in Access as well but may not because some of the functions are more advanced.
0
 

Author Closing Comment

by:eliwil
ID: 31466945
It didn't work, but that's my fault for posting it here and not in Access. Thanks for your assistance. I've reposted the question in Access.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

837 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