Clean Firstname which has special characters and numbers

Question

I have junk in my firstname field and i want to clean them . i am trying to see the names that are not valid(contains all special characters , numbers are invalid).

select firstname 
from table 
where firstname !~'[[:alpha:]]'

my query doesn't work in the right way.

Input values in the table looks like:

Firstname
---------
jack
1h7kk
$han
M.a
pac*^
___*(6&%
a

Output(should look like this )

Firstname
---------
1h7kk
$han
pac*^
___*(6&%

Your help is appreciated.

Thank You, Swathi.

Answer 1

If you are trying to get all the invalid first names, use

select name
from t 
where name !~*'^[a-z]+$'

use * for case insensitive matching.

To get valid names, use

select name
from t 
where name ~*'^[a-z]+$'

SQL Fiddle

Edit: Per OP's comment, the firstname can also have - and space character which are valid.

select name
from t 
where name ~*'(^[a-z]+\.?\-?\s*?[a-z]+\.?$|^[a-z]+$)'

SQL Fiddle with edit

Explanation:

• ^[a-z]+ starts with one or more characters in the range a-z (case insensitive)
• \. followed by an optional .
• \-? followed by an optional - character
• \s? followed by an optional space
• [a-z]+ followed by one or more characters in the range a-z (case insensitive)
• \.$ ends with an optional .

Answer 2

I'd suggest using a cursor to loop through each entry in your table, then looping through each of those character by character, testing each one with ISALPHA() to see if any of the characters in the name is not an alphabetic character. Like so:

DECLARE AlphaCursor CURSOR
FOR SELECT firstname
FROM TABLE;

DECLARE @string varchar(15);

DECLARE @index int;
DECLARE @len int;
DECLARE @char char(1);

DECLARE @IsGood Boolean;

OPEN AlphaCursor;
FETCH NEXT FROM AlphaCursor INTO @string;

WHILE @@FETCH_STATUS = 0
BEGIN

@IsGood = TRUE;

SET @index = 1;
SET @len= LEN(@string);

WHILE @index<= @len
BEGIN
SET @char = SUBSTRING(@string, @index, 1)
IF NOT ISALPHA(@char) THEN
@IsGood = FALSE;
END;

SET @index= @index+ 1;
END;

IF NOT @IsGood THEN
PRINT @string;
END;

FETCH NEXT FROM AlphaCursor INTO @string;

END;