By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Quick help with extracting a value

P: n/a
I'm hoping this is simple:

field1 | field2
abc-123
def-456
abc-123

I need to update field2 to everything that comes after the dash in field 1.
So final result would be

field1 | field2
abc-123 | 123
def-456 | 456
abc-123 | 123

thanks
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Johnny Gardner wrote:
I'm hoping this is simple:

field1 | field2
abc-123
def-456
abc-123

I need to update field2 to everything that comes after the dash in field 1.
So final result would be

field1 | field2
abc-123 | 123
def-456 | 456
abc-123 | 123

thanks


update table set field2 = Mid(field2 , InStr(1, field2 , "-") + 1)

--
[OO=00=OO]
Nov 13 '05 #2

P: n/a
Johnny Gardner wrote:
I'm hoping this is simple:

field1 | field2
abc-123
def-456
abc-123

I need to update field2 to everything that comes after the dash in field 1.
So final result would be

field1 | field2
abc-123 | 123
def-456 | 456
abc-123 | 123

thanks


You can use the "INSTR()" function to find the location of the dash and
the "MID()" function to take all the characters after the dash in the
string value in field 1 and an update query to load that value in field
2. Place the following on the update line in field 2:

Mid([field1],instr([field1],"-")+1)

Or you could do the whole process with the following SQL:

UPDATE Table1 SET Table1.field2 = Mid([field1],InStr([field1],"-")+1);

The MID function will start to read the string specified in the first
variable at the character number given in the second part of the
function. The INSTR (IN STRING)function returns the number of
characters from the start of the string till it finds the character
specified in the second part of the function "-". The +1 adds one to
the value returned by the INSTR function to start the string at the
character after the dash.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.