T-SQL for SS2000 -- get characters until a digit

I would like to get the characters until the first digit -- what is the most efficient way to do this in a T-SQL statement for SQL Server 2000?

data examples:
AB1234 -- want AB
ABC1234 -- want ABC

use field name: [fieldname]

the data type is Long Text

thank you

SOLUTION

Jeff Darling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crystal (strive4peace) - Microsoft MVP, Access

ASKER

thank you, Jeff. Can I put this function into a T-SQL statement? I cannot change the SS database -- am linking from Access and writing pass-through queries.

For instance, if I have:
SELECT myCalculatedField FROM mytable
where would it go?

Jeff Darling

This is more of an Microsoft Access question than a SQL 2000 question. Chances are you could just use REGEX in Access to do it.

Adding Access to question to attract Access experts.

crystal (strive4peace) - Microsoft MVP, Access

ASKER

thanks ... but I am NOT writing a query using Access -- only using Access to get the data ... a pass-through query means it uses T-SQL. Because it is a pass-through, it does not need ODBC to translate ... trying to speed this up using T-SQL directly.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crystal (strive4peace) - Microsoft MVP, Access

ASKER

It works! Thank you so much, Scott!

Used:
SUBSTRING(CAST(column_name AS varchar(100)), 1, PATINDEX('%[0-9]%', CAST(column_name AS varchar(100))+ '0') - 1) as BegDesc

since it is Long Text ... (and this is an old version of SS)

...

~°~ MeRRy 3rd day of ChRiStmAs