Access Database search for number(s) within a string of numbers and characters and return the exact number(s) searched for.

in Access, try something like:

SELECT * , 
IIF( INSTR(CASTID, '2011' )-1 > 0 , MID( CASTID, INSTR(CASTID, '2011' )-1 , 1) , "") as FrontChar,
IIF( INSTR(CASTID, '2011' )+4 <= len(CASTID) , MID( CASTID, INSTR(CASTID, '2011' )+4 , 1) , "") as EndChar
FROM YourTable
Where INSTR(CASTID, '2011' ) > 0
and  IIF( INSTR(CASTID, '2011' )-1 > 0 , MID( CASTID, INSTR(CASTID, '2011' )-1 , 1) , "")  in ("" , "/" )
and  IIF( INSTR(CASTID, '2011' )+4 <= len(CASTID) , MID( CASTID, INSTR(CASTID, '2011' )+4 , 1) , "") in ("" , "/")

Select allOpen in new window

Thanks, Ryan.
Works great.
However, when I try to run it in my ASP Classic SQL Statement.
I get the following error.

Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "SELECT * ,IIF(INSTR("]
'

Code line looks like this.

Set getSQL = CreateObject("ADODB.Command")
getSQL.ActiveConnection=objConn
getSQL.Prepared = true
getSQL.commandtext = "SELECT * , IIF( INSTR(CASTID, '2011' )-1 > 0 , MID( CASTID, INSTR(CASTID, '2011' )-1 , 1) , "") as FrontChar, IIF( INSTR(CASTID, '2011' )+4 <= len(CASTID) , MID( CASTID, INSTR(CASTID, '2011' )+4 , 1) , "") as EndChar FROM Media Where INSTR(CASTID, '2011' ) > 0 and IIF( INSTR(CASTID, '2011' )-1 > 0 , MID( CASTID, INSTR(CASTID, '2011' )-1 , 1) , "") in ("" , "/" ) and IIF( INSTR(CASTID, '2011' )+4 <= len(CASTID) , MID( CASTID, INSTR(CASTID, '2011' )+4 , 1) , "") in ("" , "/")
set rschID = getSQL.execute

Select allOpen in new window

Of course, in the real code, all the numbers are replaced with ? Question marks.

Are the numbers always separated by /?

If the are you could try this.

SELECT CastID
FROM Media
WHERE CastID Like "*2011/*"

Select allOpen in new window

@Norie,

I assume there could be values such as "2011" and "20119", so only "2011" is qualified.

Ryan

I think I might have misinterpreted the data, I thought this was all one value:

4807/11056/11055/11054/11053/2011/11052/11051/11050/10897/11049/20119/

Mind you, if the criteria is '2011' why the need for wildcards/Like?

by the way, I think it could be something like this as well:

4807/11056/11055/11054/11053/10897/11049/2011

so I think the presence of "/" is not compulsory

The string of data is CastID, to reference that characters ID on the Cast Table.
So, there are many cast members per video, so, I needed to add their name first, and then reference the ID number from the Media table.
So, in this case, I separated the values with the /

Thanks again, Ryan.
Great work.
Works like a charm in both now.

You can use:

SELECT CastID
FROM Media
WHERE CastID Like "*\2011\*" OR Left([CastID], Len("2011") + 1) = "2011\" OR Right([CastID], Len("2011") + 1) = "\2011" 

Select allOpen in new window

Wayne

So in the CastID field you do have a strings like this?

4807/11056/11055/11054/11053/2011/11052/11051/11050/10897/11049/20119/

How convoluted can a solution be?

Yes, Norie.
The / separates the ID's from one another.

@Gustav Brock
I tested your code and did not return any results.
Running Ryan's code, returned 3 results.

@Ryan.
Found an issue with the code.
I did a search for 815, and it found (8156)
So, it is not just grabbing the correct number all the time.

I did a search for 815, and it found (8156)
So, it is not just grabbing the correct number all the time.

it's because I made an assumption to look up for an ID with 4 chars.

you may try this instead:

compareValue = "815"

Set getSQL = CreateObject("ADODB.Command")
getSQL.ActiveConnection=objConn
getSQL.Prepared = true

getSQL.commandtext = "SELECT * , IIF( INSTR(CASTID, '" & compareValue & "' )-1 > 0 , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )-1 , 1) , '') as FrontChar, IIF( INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " , 1) , '') as EndChar FROM Media Where INSTR(CASTID, '" & compareValue & "' ) > 0 and IIF( INSTR(CASTID, '" & compareValue & "' )-1 > 0 , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )-1 , 1) , '') in ('' , '/' ) and IIF( INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " , 1) , '') in ('' , '/') "

set rschID = getSQL.execute

Select allOpen in new window

The numbers can be of any size.
From the single "1"
To multiple, "1000000"
All depends on how many records are in the database.

Running it in Access, and I am getting the error.

Data type mismatch in criteria expression.

When running in the ASP Page, it does not return any records now.
I replaced all the
'" & compareValue & "'
with the
?

SELECT * , IIF( INSTR(CASTID, ? )-1 > 0 , MID( CASTID, INSTR(CASTID, ? )-1 , 1) , '') as FrontChar, IIF( INSTR(CASTID, ? )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, ? )+" & len(compareValue) & " , 1) , '') as EndChar FROM Media Where INSTR(CASTID, ? ) > 0 and IIF( INSTR(CASTID, ? )-1 > 0 , MID( CASTID, INSTR(CASTID, ? )-1 , 1) , '') in ('' , '/' ) and IIF( INSTR(CASTID, ? )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, ? )+" & len(compareValue) & " , 1) , '') in ('' , '/')

Select allOpen in new window

worked for me in Access using codes I have posted:

compareValue = "815"

Set getSQL = CreateObject("ADODB.Command")
getSQL.ActiveConnection=objConn
getSQL.Prepared = true

getSQL.commandtext = "SELECT * , IIF( INSTR(CASTID, '" & compareValue & "' )-1 > 0 , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )-1 , 1) , '') as FrontChar, IIF( INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " , 1) , '') as EndChar FROM Media Where INSTR(CASTID, '" & compareValue & "' ) > 0 and IIF( INSTR(CASTID, '" & compareValue & "' )-1 > 0 , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )-1 , 1) , '') in ('' , '/' ) and IIF( INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " , 1) , '') in ('' , '/') "

set rschID = getSQL.execute

Select allOpen in new window

db.accdb

When running in the ASP Page, it does not return any records now.

how you provide the value for ? in your codes?

I use this.
(9 times, as there are 9 references.)
This worked on the first code you sent, that I accepted as answer.
Just not now.

strcastID = rs("catID") ' Feed in from another Query.
getSQL.Parameters.Append getSQL.CreateParameter("@CastID", adVarChar, adParamInput, 25, strcastID)

Select allOpen in new window

I don't have ASP at my working place, so I may need to test it after work ....

OK, I got it to work in ASP.
This is weird.
The original code caught all three videos by the castID.
This new code only captures 2 of the 3.

This ID for this one is: 3915

Even tested within Access, and still, only 2 of the three records show.

in that case, I would suggest you to debug the SQL statement generated, and see if something we have missed out there?

...
SQL = "SELECT * , IIF( INSTR(CASTID, '" & compareValue & "' )-1 > 0 , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )-1 , 1) , '') as FrontChar, IIF( INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " , 1) , '') as EndChar FROM Media Where INSTR(CASTID, '" & compareValue & "' ) > 0 and IIF( INSTR(CASTID, '" & compareValue & "' )-1 > 0 , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )-1 , 1) , '') in ('' , '/' ) and IIF( INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " <= len(CASTID) , MID( CASTID, INSTR(CASTID, '" & compareValue & "' )+" & len(compareValue) & " , 1) , '') in ('' , '/') "
response.write SQL
response.end
getSQL.commandtext = SQL
..

Ah, the slashes are not backslashes. This is tested successfully:

SELECT CastID
FROM Media
WHERE CastID Like "*/2011/*" OR Left([CastID], Len("2011/")) = "2011/" OR Right([CastID], Len("/2011")) = "/2011" 

Select allOpen in new window

@Gustav Brock
Yours now give the correct results

@Ryan
The last code worked great.
Once I saw the len(compareValue)
I realized I had missed it, in adding the, ? Question marks.

works great.
Thanks for your assistance as always.
Great code.

@Dale Fye
That was simple and easy.
I like this one.
One Query call is always nice.

glad I could help.