Find Alphabetical records only

15 Jul 2005 3:47 PM

microsoft.public.dotnet.languages.vb

Hi All,

Here is the scenario.

Table contains vendor codes and these are either numeric or mix of
alphabets and numbers.

Such as : 56710
BAC67
ABF11
34567

I want to query which will give me the results which starts with
alphabets only.

CREATE TABLE [dbo].[Table1] (
[Vendor] [char] (7) NOT NULL

) ON [PRIMARY]
GO

Insert Table1(Vendor)
select '5945'
union all
select 'GQ123'
union all
select 'ABC31'
union all
select '45678'

results wanted:

GQ123
ABC31

I will highly appreciate your help.

thanks a million in advance.

Best regards,

mamun

15 Jul 2005 3:51 PM

Stu

You can use character-specific wildcards:

SELECT Vendor
FROM TABLE1
WHERE Vendor LIKE '[a-z]%'

HTH,
Stu

15 Jul 2005 4:06 PM

Aaron Bertrand [SQL Server MVP]

> You can use character-specific wildcards:
>
> SELECT Vendor
> FROM TABLE1
> WHERE Vendor LIKE '[a-z]%'

I like to suggest [A-Za-z] in case they have a case-sensitive collation.

You can probably also make a case for accented characters but usually this
is overkill. :-)

A

15 Jul 2005 3:51 PM

Aaron Bertrand [SQL Server MVP]

CREATE TABLE dbo.Splunge
(
a VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT dbo.Splunge SELECT '56710'
INSERT dbo.Splunge SELECT 'BAC67'
INSERT dbo.Splunge SELECT 'ABF11'
INSERT dbo.Splunge SELECT '34567'

SELECT * FROM Splunge WHERE a LIKE '[^0-9]%'
SELECT * FROM Splunge WHERE a LIKE '[A-Za-z]%'
GO

DROP TABLE dbo.Splunge
GO

Show quote

"microsoft.public.dotnet.languages.vb" <mamun***@hotmail.com> wrote in
message news:1121442464.430403.96900@o13g2000cwo.googlegroups.com...
> Hi All,
>
> Here is the scenario.
>
> Table contains vendor codes and these are either numeric or mix of
> alphabets and numbers.
>
> Such as : 56710
> BAC67
> ABF11
> 34567
>
> I want to query which will give me the results which starts with
> alphabets only.
>
> CREATE TABLE [dbo].[Table1] (
> [Vendor] [char] (7) NOT NULL
>
> ) ON [PRIMARY]
> GO
>
>
>
> Insert Table1(Vendor)
> select '5945'
> union all
> select 'GQ123'
> union all
> select 'ABC31'
> union all
> select '45678'
>
>
> results wanted:
>
> GQ123
> ABC31
>
>
> I will highly appreciate your help.
>
> thanks a million in advance.
>
> Best regards,
>
> mamun
>

15 Jul 2005 4:01 PM

JT

to filter:

where isnumeric(x) = 0

to sort with non-numeric first and then numeric:

order by
isnumeric(x),
x

Show quote

"microsoft.public.dotnet.languages.vb" <mamun***@hotmail.com> wrote in
message news:1121442464.430403.96900@o13g2000cwo.googlegroups.com...
> Hi All,
>
> Here is the scenario.
>
> Table contains vendor codes and these are either numeric or mix of
> alphabets and numbers.
>
> Such as : 56710
> BAC67
> ABF11
> 34567
>
> I want to query which will give me the results which starts with
> alphabets only.
>
> CREATE TABLE [dbo].[Table1] (
> [Vendor] [char] (7) NOT NULL
>
> ) ON [PRIMARY]
> GO
>
>
>
> Insert Table1(Vendor)
> select '5945'
> union all
> select 'GQ123'
> union all
> select 'ABC31'
> union all
> select '45678'
>
>
> results wanted:
>
> GQ123
> ABC31
>
>
> I will highly appreciate your help.
>
> thanks a million in advance.
>
> Best regards,
>
> mamun
>

15 Jul 2005 4:11 PM

Aaron Bertrand [SQL Server MVP]

> to filter:
>
> where isnumeric(x) = 0

He was asking about the first character only, not the whole value.

However, he wasn't clear about non-alphanumeric characters, such as ?, & and
@. So isnumeric(left(x,1)) might work, but it might not.