Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

update query to split field

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
eliwil
Asked:
eliwil
  • 4
  • 3
1 Solution
 
brad2575Commented:
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
 
brad2575Commented:
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
 
eliwilAuthor Commented:
I'm getting a syntax error. Could you give me the complete statement? the table is TestResults and the field is Lot.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
brad2575Commented:
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
 
eliwilAuthor Commented:
Sorry, I thought the question was posted to Access SQL rather than just SQL syntax. Should have double checked. Appreciate your assistance.
0
 
brad2575Commented:
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
 
eliwilAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now