[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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.)
0
LoriLHS
Asked:
LoriLHS
  • 8
  • 5
  • 4
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
HainKurtSr. System AnalystCommented:
oops, is that access :) sorry, solution is for SQL
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:


 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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
see image

Are you in the ANTELOPE VALLEY ?

mx
Capture1.gif
0
 
LoriLHSAuthor Commented:
Done. Thank you.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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