How do I strip a number from a string

I have a field named Process in my table.  In the field is text: Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLEY.  What would be the sql to be used in a query to strip the 15 out of the string.  The number could be any anything (7 or 300 or 12 or 1000, etc.)
LoriLHSAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
If you just want the numbers to remain, try thisL

SELECT Val(Mid([FIELDX],InStr([FIELDX],"% of ")+5)) AS Expr1
FROM Table1
WHERE (((Table1.FIELDX) Is Not Null));


mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Is there any pattern to that number before and/or after ... like those two dashes?

Like Obstetrics, Gynecology-- 100% of 9999-- RANCHO CUCAMONGA  :-)

MX
0
 
LoriLHSAuthor Commented:
yes. '% of'  before and '--' after
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
HainKurtSr. System AnalystCommented:
try:

declare @p as varchar(100)='Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLE';
select SUBSTRING(@p, charindex('% of',@p)+5, charindex('--',@p, charindex('% of',@p))-charindex('% of',@p)-5),

-->

15
0
 
HainKurtSr. System AnalystCommented:
oops, is that access :) sorry, solution is for SQL
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Example:;

Left("Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLEY",instrRev("Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLEY","--")-3) & Mid("Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLEY",instrRev("Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLEY","--"))

Returns this.
Obstetrics, Gynecology-- 100% of -- ANTELOPE VALLEY

Is that what you want?

mx
0
 
HainKurtSr. System AnalystCommented:
in Access we use InStr and parameters reversed and use Mid instead of SubStr

0
 
LoriLHSAuthor Commented:
Sorry, I just want the 15 only.  The field is called Process, wouldn't that be used in the sql instead of the 'Obstetrics, Gynecology-- 100% of 15-- ANTELOPE VALLEY'
0
 
HainKurtSr. System AnalystCommented:
access version

select mid(
Process ,
instr(Process ,'% of')+5,
instr(mid(Process ,instr(Process ,'% of')+5, len(Process )),'--')-1
) from myTable
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So, in a query ... to display w/o :

SELECT Left([FIELDX],InStrRev([FIELDX],"--")-3) & Mid([FIELDX],InStrRev([FIELDX],"--")) AS Expr1
FROM Table1
WHERE (((Table1.FIELDX) Is Not Null));

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"wouldn't that be used in the sql instead of the "

You want to retain the 15?  You implied strip it out ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


 What would be the sql to be used in a query to strip the 15 out of the string

Also, if Process  can be Null, the Accepted Solution will error out.

mx
0
 
LoriLHSAuthor Commented:
DatabaseMX, you are right.  I thought I could fix the error out.  You deserved the points, sorry.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can hit the Request Attention button and re assign.

thx.mx
0
 
LoriLHSAuthor Commented:
I cannot find the request attention button...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
see image

Are you in the ANTELOPE VALLEY ?

mx
Capture1.gif
0
 
LoriLHSAuthor Commented:
Done. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.