Solved

update query to split field

Posted on 2008-06-13
7
217 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
[X]
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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 75
Microsoft Access Delete all Records from table but Max 2 28
SQL Select Query help 1 36
SQL query 7 11
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…

740 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