Ignoring symbols and numbers

[kloner]

Hi all,

I am trying to come up with a way to ignore numbers and symbols in my query for a particular field.

If i have a word like "4-Acetamidophenylboronic" I want to ignore all numbers and symbols.

For example I want my query to display the word "4-Acetamidophenylboronic" when I do something like

SELECT * FROM TBL_PRODUCT WHERE (TBL_PRODUCT.NAME LIKE 'A%') ORDER BY SORT

Now I want to somehow ignore the "4-" and still display "4-Acetamidophenylboronic"

I'm using SQL Server 7.0



SOME SAMPLE DATA
====================
Bis(neopentylglycolato)diboron
Bis(pinacolato)diboron
Bis((+)-pinanediolato)diboron
Bis((-)-pinanediolato)diboron
4-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)acetanilide
4-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)phenol
3-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)phenol
2-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)phenol
2-Methoxy-4-(4,4,5,5-tetramethyl-1,3,2-dioxaborolan-2-yl)phenol
4-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)aniline
2-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)aniline
2,6-Dimethyl-4-(4,4,5,5-tetramethyl-1,3,2-dioxaborolan-2-yl)phenol
cis-1,2-Bis(4,4,5,5-tetramethyl-1,3,2-dioxaborolan-2-yl)stilbene
1-cis-1,2-Bis(4,4,5,5-tetramethyl-1,3,2-dioxaborolan-2-yl)heptene
4-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)toluene
tert-Butyl N-(4-iodophenyl)carbamate
4-Iodopyrazole
Ethyl 4-iodobenzoate
Phenyl boronic acid
(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)benzene
tert-Butyl-N-[4-(4,4,5,5-tetramethyl-1,3,2-dioxaborolan-2-yl)phenyl]-carbamate
Ethyl 3-(4,4,5,5-tetramethyl-1,3,2-dioxaborolan-2-yl)benzoate
tert-Butyl N-(4-bromophenyl)-carbamate
4-Bromophenol
4-Iodophenol
4-Bromoaniline
4-Iodoaniline
2,6-Dichloropurine
4-Tolylboronic acid
4-Fluorophenylboronic acid
4-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)fluorobenzene
kloner

 

[MeanGreen]

You might try this. It will display any row where the 'A' is found in the column:

SELECT * FROM TBL_PRODUCT WHERE charindex('A',TBL_PRODUCT.NAME) > 0 ORDER BY SORT

Hope this helps.

 

[kloner]

Thanks for that, however I am still after a solution to display all 'NAMES' starting with 'A' (or any other letter), but ignoring all numbers and symbols until I reach the 'A'.


eg. 'T' would be ...


2-(4,4,5,5-Tetramethyl-1,3,2-dioxaborolan-2-yl)phenol

ignoring the "2-(4,4,5,5-" and then we get the 'T' kloner

 

[SQLSister]

Have you considered using a full-text search? This will search for a particular phrase anywhere in the field.

It's kind of complicated to talk about setting up, so I suggest you read about it first in Book Online and see if it will do what you want. Then come on back and we can discuss how to implement.

 

[tlbroadbent]

Try this.

Select *
From TBL_PRODUCT.NAME
Where substring(Name,patindex('%[A-Z]%',Name),1)='B' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.