Fetching fields starting with a number, etc.

[Guest_imported]

I have a table containing information about stories (title, author, etc). How can I phrase my SELECT statement to retrieve all stories whose titles begin with a number?

Also, some titles begin with one or more non-alphanumeric characters. Is there a way to ignore the non-alphanumeric characters when doing a SELECT?

 

[gheist]

select field from table where field matches '[0-9]*'


it is very slow

 

[piti]

wel i would try this one to retrieve titles begining with a number

select * from table where titlefield regexp "^[0-9]"

and to retrieve all titles starting with an alphanumeric character

select * from table where titlefield regexp "^[[:alnum:]]"

hope this helps

 

[Guest_imported]

Thank you for the help so far. The one for retrieving titles beginning with a number is working beautifully.

Ignoring punctuation needs to be more specific than:
select * from table where titlefield regexp "^[[:alnum:]]"

My script needs to retrieve all titles in which the first alphanumeric character is a specified letter. For example, if two titles in my table are Fiddlesticks and "... Fee Fie Foe Fum" (including the quotes), they should both show up on a search for stories beginning with F.

 

[piti]

i think this should work in that case

select * from table where titlefield regexp "(^F)|(^[^[:alnum:]]+F)"

 

[KarveR]

Simpler than that, SELECT * from table where titlefield like 'f%'
will return all titles starting with f.

If you want a title starting with f and is 6 characters long, you can do f_____ (5 undercores, each one represents 1 character of anything. ***************************************
Party on, dudes!

 

[Guest_imported]

regexp "(^F)|(^[^[:alnum:]]+F)" worked perfectly! Thank you very much everyone.