Solved

SQL Query For Alpha or Numerical Characters

Posted on 2003-11-26
9
1,419 Views
Last Modified: 2011-04-14
Hello,
I have a column in my database called 'Atozlisting', it is filled with only i character in each row of data.  the character maybe an alpha character or a Numeral.  i.e
a or g or f or 5 or 3 or j... etc

I'm trying to write two SQL statements.  The first one will return only records that have an alpha characters as their value and the second one will return records that have a numerals as their value

thanks
:-)
0
Comment
Question by:show_t
  • 5
  • 4
9 Comments
 
LVL 6

Expert Comment

by:lausz
ID: 9825063
Try this

All together

select case when isnumeric(Atozlisting) = 1 then 'Num' else 'alpha' end
from yourtable

or

Select Atozlisting  --numeric
from yourtable
where isnumeric(Atozlisting) = 1

and

Select Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0
0
 

Author Comment

by:show_t
ID: 9825358
thanks for your help,

i've also tried adding 'DISTINCT' in the SQL query in an attempt to NOT return any records with the same values but this doesn't work.  is 'distinct' only used with joins ?

thanks again
0
 
LVL 6

Expert Comment

by:lausz
ID: 9826072
This must work.

Select distinct Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:show_t
ID: 9826227
Yeah it works just fine, i just wanted to know if you also knew how to NOT return records that have the same value

Thanks
0
 
LVL 6

Expert Comment

by:lausz
ID: 9826654
Do you need more help ?
0
 

Author Comment

by:show_t
ID: 9827842
yes please, about the 'Distinct' issue
0
 
LVL 6

Expert Comment

by:lausz
ID: 9831269
Can you post your final query...(with the distinct not running) ?
0
 

Author Comment

by:show_t
ID: 9832355
Hello,

Basically, when i do this :

Select distinct Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0

IT WORKS! i get :
A
A
A
B
B
C
F
G
G
J
K
L
M
M

However,  i need the query to only return :
A
B
C
F
G
J
K
L
M

Do you see what i mean, without duplicates.

thanks
:-)
0
 
LVL 6

Accepted Solution

by:
lausz earned 125 total points
ID: 9833025
I don't understand ..

Try this two options :

Select distinct ltrim(Atozlisting) -- alpha
from yourtable
where isnumeric(Atozlisting) = 0

or


Select Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0
group by Atozlisting
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question