Solved

update query to split field

Posted on 2008-06-13
7
196 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

'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 …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now