Pulling text from a field

[Leslie67]

Here's my issue:

I need to write this in SQL, not VBA and i know that it's probably very simple, but I'm not seeing it right now. I'm not large with the text manipulation...

I need to compare a four digit number from one table to a four digit number in a 2nd table. However, the four digit number in the second table is buried in a long string.

I know that I need to use a Mid() and probably an Instr() function to pull out the four digits, but I can't get the syntax right. This is the setup: slash followed by a word or two, followed by another slash, followed by the four digits that I want, followed by slashes with other information.

/san diego/####/otherstuff/more other stuff/
/new york/####/morestuff/more more stuff/
/chicago/####/gunky/more gunky

 

[glalsop]

Given these constraints:

1) First character is always "/"
2) Number is always 4 digits long

, this will extract the 4-digit number:

mid("/san diego/####/otherstuff/more other stuff/",instr(2,"/san diego/####/otherstuff/more other stuff/","/")+1,4)

Good luck

-Gary

 

[PHV]

Something like this ?
Mid([Field Name],1+InStr(2,[Field Name],"/"),4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[glalsop]

Oops, in the context of the query, you will of course need to use your field name:

mid(YourLongTextField,instr(2,YourLongTextField,"/")+1,4)

-Gary

 

[Leslie67]

That was perfect, guys!

Thank you so much!