Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update query to split field

Posted on 2008-06-13
7
Medium Priority
?
226 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

705 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