CODEWITHSUNDEEP
mysqlsql
seeusoon
seeusoon

Reputation: 45

Capture letters in field containing letters+numbers

I am trying to clean some data in my database.

I have a column that contains both letters and numbers.

I would like to build a query that will catch all the field for which there is more than 4 letters in a row.

1293.8093CHINA34324            -- (YES)
MY32498VN34983-294TH32498PH    -- (NO)
WORLD_3244932                  -- (YES)
9HEY850249.243943              -- (NO)
32484359-78049                 -- (NO)
3294832.49234PROGRAMMATION     -- (YES)

Thx a lot for your help.

Upvotes: 1

Views: 42

Answers (2)

anjali
anjali

Reputation: 84

This is a query for sql server:- select name from (select * from [tests].[dbo].[emplo] where len(name)>=4) as s where name like '%[a-z][A-Z]%'

Upvotes: 0

e4c5
e4c5

Reputation: 53734

REGEXP to the rescue

SELECT * FROM my_table WHERE my_column REGEXP '[a-zA-Z]{4}'

My Original query said [A-Z] Tim, kindly edited and added a case insensitive regex. The original regex was based on the assumption that your mysql database has a case insensitive collation (which is the default)

Upvotes: 4

Related Questions