check Alphanumeric string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

check Alphanumeric string

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-07-24 : 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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-24 : 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

22864 Posts

Posted - 2012-07-24 : 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

83 Posts

Posted - 2012-07-24 : 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

22864 Posts

Posted - 2012-07-24 : 06:43:33

Have you tried TC's method that would be simple

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-24 : 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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 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

83 Posts

Posted - 2012-07-24 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources