| Author |
 Topic  |
|
|
pushp82
Yak Posting Veteran
68 Posts |
 Posted - 07/24/2012 : 05:40:32
|
Hi,
I need to check a string in procedure if that is alphanumeric or not
means:
123aaa : Accepted
aaaaaa : not accepted
123123 : not accepted
how to fulfill this condition?
No special character allowed also.
Please help!
Pushkar
|
Edited by - pushp82 on 07/24/2012 05:53:05
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 07/24/2012 : 05:43:32
|
try this?
BEGIN TRAN
DECLARE @sample TABLE (
[value] VARCHAR(MAX)
)
INSERT @sample ([value])
VALUES ('123aaa')
, ('aaaaaa')
, ('123123')
SELECT * FROM @sample
WHERE
[value] LIKE '%[a-Z]%'
AND [value] LIKE '%[0-9]%'
ROLLBACK
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 07/24/2012 : 05:44:30
|
col like '%[0-9]%' AND col like '%[^0-9]%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 07/24/2012 : 06:05:28
|
quote:
Originally posted by webfred
col like '%[0-9]%' AND col like '%[^0-9]%'
No, you're never too old to Yak'n'Roll if you're too young to die.
May not be accurate for data like 123#$7
Madhivanan
Failing to plan is Planning to fail |
|
|
|
pushp82
Yak Posting Veteran
68 Posts |
Posted - 07/24/2012 : 06:27:42
|
I just added
NOT LIKE '%[!@#$%^&*()_+=-.":;><,/?\|]%'
So the statement is as below
IF(@string LIKE '%[^a-z]%' AND @string LIKE '%[^0-9]%' AND @string NOT LIKE '%[!@#$%^&*()_+=-.":;><,/?\|]%')
thanks for the quick responce guys, made my days
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 07/24/2012 : 06:43:33
|
Have you tried TC's method that would be simple
Madhivanan
Failing to plan is Planning to fail |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 07/24/2012 : 06:47:11
|
quote:
Originally posted by madhivanan
quote:
Originally posted by webfred
col like '%[0-9]%' AND col like '%[^0-9]%'
No, you're never too old to Yak'n'Roll if you're too young to die.
May not be accurate for data like 123#$7
Madhivanan
Failing to plan is Planning to fail
I thought EXACTLY this is accurate 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 07/24/2012 : 07:00:23
|
I think OP edited post to include a requirement not to include special characters.
This then is probably the nicest way of doing it
DECLARE @sample TABLE (
[value] VARCHAR(MAX)
)
INSERT @sample ([value])
VALUES ('123aaa')
, ('aaaaaa')
, ('123123')
, ('asdsad"£"!3sad')
, ('a+3')
SELECT * FROM @sample
WHERE
[value] LIKE '%[a-Z]%'
AND [value] LIKE '%[0-9]%'
AND [value] NOT LIKE '%[^0-Z]%'
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
pushp82
Yak Posting Veteran
68 Posts |
Posted - 07/24/2012 : 07:39:18
|
you were actually perfect TC thanks for the logic....
quote:
Originally posted by Transact Charlie
I think OP edited post to include a requirement not to include special characters.
This then is probably the nicest way of doing it
DECLARE @sample TABLE (
[value] VARCHAR(MAX)
)
INSERT @sample ([value])
VALUES ('123aaa')
, ('aaaaaa')
, ('123123')
, ('asdsad"£"!3sad')
, ('a+3')
SELECT * FROM @sample
WHERE
[value] LIKE '%[a-Z]%'
AND [value] LIKE '%[0-9]%'
AND [value] NOT LIKE '%[^0-Z]%'
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
|
|
|
| |
Topic |
|
check Alphanumeric string
