CipherIS
asked on
SQL CASE Statement Check to see if Field Contains Alpha Characters
How can I use a CASE statement to check if a field contains alpha characters?
Ex
CASE F1 CONTAINS [A], [B] ....
Ex
CASE F1 CONTAINS [A], [B] ....
Can you provide some sample data please? What exactly are you trying achieve would be nice to know. Why I say this? Simple. For example, if you are trying to see if there is a alpha in otherwise expected to be a numeric value, there are functions to see if a value is numeric. Then this function can be used with NOT operator. Hope you get the point.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And here is a complete demonstration...time gone in typing...
This is case insensitive.
This is case insensitive.
declare @temp table
(
[column1] varchar(10)
)
insert into @temp values
('A5689')
,('i5689')
,('5689')
,('56B89')
,('5689')
,('56J89')
,('2017-09-01')
,('5689L')
select *
from @temp
where [column1] like '%[A-Za-z]%'
select [column1], case when [column1] like '%[A-Za-z]%' then 1 else 0 end [HasAlpha]
from @temp
Just a comment: CASE is an expression, not a statement.
Do you mean only alpha chars? Including a space as valid as well:
SELECT F1, CASE WHEN F1 LIKE '%[^a-z ]%' THEN 'Not only alpha' ELSE 'only alpha' END
...
SELECT F1, CASE WHEN F1 LIKE '%[^a-z ]%' THEN 'Not only alpha' ELSE 'only alpha' END
...