how do I check if a text is digit or uppercase?

Question

0.00/5 (No votes)

See more:

Hi everyone!
I need something that check if a digit or uppercase or mixed.
I can't allow character like !,<,#
example
1) 'aA1'-->no
2) 'A1' -->ok
3) '1' -->ok
4) '1a' -->no

I have tried with regular expression :

SQL

DECLARE @PROVA AS VARCHAR(50)=''
SELECT CASE WHEN @PROVA  LIKE  '%[A-Z]%'   Collate Latin1_General_CS_AI
          THEN 'ok'								                                          ELSE 'NO'
END

but it doesn't work in every case.
Can you help me?
thank you

Posted 1-Sep-15 21:51pm

gregorio89

Updated 1-Sep-15 22:10pm

v2

Add a Solution

2 solutions

Solution 1

That will only check if there is a single char that is uppercase.

If you want to check if the whole string is uppercase then compare it to itself in uppercase:

SQL

SELECT 
   CASE WHEN @PROVA  = UPPER(@PROVA)
        THEN 'ok'
        ELSE 'NO'
END

UPDATE: OP needs to restrict special chars.

There are a couple of options depending on your needs:

1: Parser function
This is probably the simplest solution. Create a function that checks each char of the string. It's easy to implement and maintain, but it can be very slow if you perform the operation on a large enough table

2: Regular Expressions:
A little trickier to implement but very powerful in the right hands. Take a look at this article for more:
https://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/[^]

Hope that helps ^_^
Andy

Posted 1-Sep-15 22:03pm

Andy Lanng

Updated 1-Sep-15 22:23pm

v2

Comments

gregorio89 2-Sep-15 4:09am

It isn't good for me because in this way you allow character like !,<,# etc.
I need some for allow only the letters in uppercase and digit

Andy Lanng 2-Sep-15 4:20am

hmm - I see few other options that to use regular expressions. I'll update my solution

Andy Lanng 2-Sep-15 4:23am

Updated the solution. Let me know if you need more detail on anything like the parser function ^_^

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gregorio89 · Accepted Answer · 2015-09-01T23:41:00

I have done in this way

SQL

DECLARE @position int
declare @string char(15)
declare @error int,@no_error int
set @error =0
SET @position = 1
SET @string = '123A5'
IF LEN(@string)=5
BEGIN
WHILE @position <= LEN(@string)
   BEGIN
   if ASCII(SUBSTRING(@string, @position, 1)) between 65 and 90 or ASCII(SUBSTRING(@string, @position, 1)) between 48 and 57
   set @no_error=1
   else
   set @error=2
   SET @position = @position + 1
   END
END
ELSE
set @error=2
select @error