Solved

How do I strip a number from a string

Posted on 2011-09-23
19
213 Views
Last Modified: 2012-05-12
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
Comment
Question by:LoriLHS
  • 8
  • 5
  • 4
19 Comments
 
LVL 75
ID: 36590030
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
 

Author Comment

by:LoriLHS
ID: 36590045
yes. '% of'  before and '--' after
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36590070
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 36590077
oops, is that access :) sorry, solution is for SQL
0
 
LVL 75
ID: 36590091
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 36590101
in Access we use InStr and parameters reversed and use Mid instead of SubStr

0
 

Author Comment

by:LoriLHS
ID: 36590116
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 36590130
access version

select mid(
Process ,
instr(Process ,'% of')+5,
instr(mid(Process ,instr(Process ,'% of')+5, len(Process )),'--')-1
) from myTable
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 75
ID: 36590143
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
 
LVL 75
ID: 36590154
"wouldn't that be used in the sql instead of the "

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

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 36590177
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
 
LVL 75
ID: 36590199


 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
 

Author Comment

by:LoriLHS
ID: 36590239
DatabaseMX, you are right.  I thought I could fix the error out.  You deserved the points, sorry.
0
 
LVL 75
ID: 36590258
You can hit the Request Attention button and re assign.

thx.mx
0
 

Author Comment

by:LoriLHS
ID: 36590277
I cannot find the request attention button...
0
 
LVL 75
ID: 36590308
see image

Are you in the ANTELOPE VALLEY ?

mx
Capture1.gif
0
 

Author Comment

by:LoriLHS
ID: 36590329
Done. Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why can't I get my report to SORT correctly? 5 26
ms/access hyperlink/ftp 7 31
data analyst 3 48
Access 2010 Query Syntax 5 15
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now