-4

How to find the alphanumeric string of 9 character in sql server with or without special charatcer (other than alphanumeric charatcer like _ or /) e.g. I have column name USER. In this column I have to select values having only alphanumeric characters like 123abc , 234def and not 23*kl or 25/bz.

user3751203
  • 1
  • 1
  • 2

2 Answers2

0

try this, 

select * from [tbkname] where len([columnname]) = 9 AND columnname like '%[^a-zA-Z0-9]%'
Friyank
  • 469
  • 3
  • 8
  • Thanks Friayank! This is seems to be exact answer. But if I want the values having other characters than alphanumeric values........ not like '%[^a-zA-Z0-9]%' will work or we have to add some more into it? – user3751203 Jun 18 '14 at 06:58
  • u mean to say special characters ? – Friyank Jun 18 '14 at 07:07
  • Yes special characters like *, $,/ etc. – user3751203 Jun 18 '14 at 07:19
  • its simple just change the regular expression from alphanumeric to special character expression i.e '%[^.*?[!#$%&()*+,\-./:;<=>?@[\\\]^`{|}~]+.*?$]%' – Friyank Jun 18 '14 at 07:21
0

Are you expecting something like the following,

DECLARE @InputString NVARCHAR(MAX) = 'D!E#$M&*O_@&1@&'
DECLARE @SrchExpr VARCHAR(255)
SET @SrchExpr =  '%[^a-z0-9]%'
    WHILE PatIndex(@SrchExpr, @InputString) > 0
        SET @InputString = Stuff(@InputString, PatIndex(@SrchExpr, @InputString), 1, '')
    SELECT @InputString

EDIT: For more generalized solution refer here.

Community
  • 1
  • 1
BAdmin
  • 927
  • 1
  • 11
  • 19
  • Excellent! But I wonder whether we can present it into simple.sql server query instead of T-Sql. Also I have to check if the number of characters in string are 9 – user3751203 Jun 18 '14 at 06:56