select query with numbers

[namtax]

i have an index on my site, which select bands by the letter that is clicked

<cfquery name="results" datasource="#APPLICATION.DataSource#">
SELECT *
FROM artist_art
WHERE bandname_art like '#url.firstletter#%'
</cfquery>
http://www.musicexplained.co.uk/index.cfm
thing is, im trying to group all bands that begin with a number together, and am unsure of the symbol which refers to "all numbers" in coldfusion..
Have looked everywhere, but this place seems to be the only place with the answers lately, so...

thanks

[Dan_Bracuk]

You can select substrings in most dbs. The exact syntax depends on the db of course. In Oracle, it would be

where substr(bandname, 1, 1) in ('0','1', ... '9')

[namtax]

ill have a look for substrings in access, see if theres a similar thing there...
thanks

[namtax]

there doesnt seem to be an alternative for this substring query in access...could you perform a similar thing using a cfset maybe....creating the list of numbers there??//

[paross1]

In Access, take a look at the Left(string, length) or Mid(string, start[, length]) functions.

Phil

[namtax]

hi phil, will those functions allow me to achieve what i was originally asking....o have used similar function before..

thanks

[paross1]

I'm not sure what you are trying to do, exactly. These particular functions are just the method that you would use in Access to query just a part of a string (substring). What you do with that substring depends on what you ultimately want to do, which isn't very clear to me. I was just responding to your post that stated that there doesn’t seem to be an alternative for this substring query in access.

Phil

[namtax]

I only got the substring thing from dan, never heard of them before....
anyway, on my site i have an index that works fine with letters.....when you click on a letter on the index page, this letter gets passed to the url, which then powers the query

SELECT *
FROM artist_art
WHERE bandname_art like '#url.(a-Z)#%'

i now need to cover numbers in this index as well, as some musicians band names start with numbers.......this website achieves what im referring to
http://songmeanings.net/ using the # symbol in the index to select any artist whose name starts with a number

thus what im looking for is a symbol that refers to all numbers...so i can say

SELECT *
FROM artist_art
WHERE bandname_art like '#url.(anynumber)#%'

[Guest · Accepted solution]

are you passing a specific number in the URL? if you are, it's the exact same code that you have (WHERE bandname_art LIKE '%#url.firstLetter#').

if you're going to use the # sign and want to get bands that start with any number 0-9, check to see if access supports regex. in SQL Server, the following would work:

WHERE bandname_art LIKE '[0-9]%'

can't vouch for Access tho.

EDIT: if Acces won't support it, at worst your query would look like:

WHERE bandname_art LIKE '0%' OR bandname_art LIKE '1%' OR bandname_art LIKE '2%' ...

-or -

WHERE left(bandname_art, 1) IN ('0','1','2','3','4','5','6','7','8','9','0')

(the latter solution there is I think what Dan was trying to steer you towards)

[namtax]

WHERE left(bandname_art, 1) IN ('0','1','2','3','4','5','6','7','8','9','0')

beautiful.....yes that works now......
do i mark cj or dan as the answer though?

[Guest]

give it to dan. he's got seniority 🙂

[paross1]

What is wrong with doing it like this?

<cfquery name="results" datasource="#APPLICATION.DataSource#">
SELECT *
FROM artist_art
WHERE LEFT(bandname_art , 1) = '#url.firstletter#'
</cfquery>

Phil

[Guest]

i think he was looking for something to select all bands that start with a numeric value (-any- numeric value) if a certain condition is met.