Rule that allow number and uppercase character

Last Post 29 Aug 2007 09:37 AM by SwePeso. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ConKi
New Member
New Member

--
28 Aug 2007 09:11 PM
Greeting All,
I need to create a rule that allow any number and ONLY upper case character. Note that the rule allow number and upper case occurs any where in the string.
So far, I got:

create rule my rule as
@value like '%[0-9]%' and @value not like '%[a-z]%'

With the above rule, I don't get what I expect to.

Please help.

Thanks,

ConKi

SwePeso
New Member
New Member

--
28 Aug 2007 09:54 PM
WHERE Col1 NOT LIKE '%[^0-9A-Z]%'
ConKi
New Member
New Member

--
28 Aug 2007 10:14 PM
Thank you Pesomannen,
With your help, I put:
create rule myrule as
len(@value) <= 6
and @value NOT LIKE '%[^0-9A-Z]%'

As a test, lower case still accept. Ex: aAa90 is still OK. What I want is absolutely no lower case at all.
SwePeso
New Member
New Member

--
29 Aug 2007 12:54 AM
DECLARE @Sample TABLE (ID INT, data VARCHAR(50))

INSERT @Sample
SELECT 1, 'aAa90 ' UNION ALL
SELECT 2, 'aAa90' UNION ALL
SELECT 3, 'A90' UNION ALL
SELECT 4, 'A90 ' UNION ALL
SELECT 5, '12A3490' UNION ALL
SELECT 6, '9' UNION ALL
SELECT 7, 'A' UNION ALL
SELECT 8, '9B' UNION ALL
SELECT 9, '9.B'

SELECT *,
CASE
WHEN DATALENGTH(data) <= 6 AND data COLLATE SQL_Latin1_General_Cp437_BIN NOT LIKE '%[^0-9A-Z]%' THEN 1
ELSE 0
END AS Valid
FROM @Sample



Output is

ID data Valid
1 aAa90 0
2 aAa90 0
3 A90 1
4 A90 0
5 12A3490 0
6 9 1
7 A 1
8 9B 1
9 9.B 0
ConKi
New Member
New Member

--
29 Aug 2007 05:50 AM
Thanks Peter,

What I need is a rule to bind to a UDT or a check constraint to enforce a business rule. So far, I tried:

The business rule is the value will not be bigger than 16 and it will allow number and CAPITAL letter only.

create rule myrule as
len (@value) <= 16 and @value in ('A', 'B', 'C', '0', '1', '2')

When I insert value 'A9' to the table and it does not like it. Don't know why.

Please help.

Thanks,

ConKi
SwePeso
New Member
New Member

--
29 Aug 2007 09:37 AM
len (@value) <= 16 and @value not like '%[^ABC012]%'
ConKi
New Member
New Member

--
29 Aug 2007 11:39 AM
That is it. Thanks a lot Peter. Here is my rule:

create rule myrule
as
len(@value) <= 16 and @value not like '%[^A-Z0-9]%'

ConKi.
SwePeso
New Member
New Member

--
02 Sep 2007 11:43 AM
Thanks for the support Peter!

But this is a case where I think OP did not really want any help. I believe he only wanted support for his self-invented method.

//Peter
You are not authorized to post a reply.

Acceptable Use Policy