Content without number

[OldMan]

Hello

 

My field contains text and number (sometimes), what i want is find fields without numbers... ( like this 2000, 1987, etcc, always with 4 number)..

 

How i can find fields without numbers?

 

Table: post

Column: tag

[justsomeguy]

Depending on which database system you're using, one way might be to use regular expressions. MySQL has the REGEXP operator, for example:

 

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

[davej]

Perhaps he means a query using LIKE?

 

http://www.w3schools.com/sql/sql_like.asp

 

http://www.w3schools.com/sql/sql_wildcards.asp

SELECT * FROM table1WHERE col1 NOT LIKE '%[0-9][0-9][0-9][0-9]';

Edited December 19, 2013 by davej

[OldMan]

 

Perhaps he means a query using LIKE?

 

http://www.w3schools.com/sql/sql_like.asp

 

http://www.w3schools.com/sql/sql_wildcards.asp

SELECT * FROM table1WHERE col1 NOT LIKE '%[0-9][0-9][0-9][0-9]';

 

Thanks, but don't work correctly.. my content is similar to this:

1º field - 2013, Jonh, Mickey, Max2º field - Mix, 1989, Software, games

Edited December 19, 2013 by OldMan

[davej]

And you want to find any field lacking a four-digit number?

SELECT *FROM table1WHERE col1 NOT LIKE '%[0-9][0-9][0-9][0-9]%';

Edited December 19, 2013 by davej

[OldMan]

 

And you want to find any field lacking a four-digit number?

SELECT *FROM table1WHERE col1 NOT LIKE '%[0-9][0-9][0-9][0-9]%';

 

I also tried that.

 

I want find fields without "2002", "1998", etc.. (all number with 4 digits)

[davej]

What database version?

[OldMan]

http://prntscr.com/2canqf

[OldMan]

I solve my problem with this query:

select id, autor from table WHERE col NOT LIKE '2%%%' AND col NOT LIKE '1%%%';

Edit:

 

I tried this too, but don't work.

http://www.go4expert.com/articles/regular-expressions-mysql-t2337/

Edited December 19, 2013 by OldMan

[davej]

You might experiment with the use of _ rather than %, although I guess it is meaningless unless we can detect numeric digits. '%1%' or '%2%' is really all we can do.

 

http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#function_strcmp

Edited December 19, 2013 by davej

[justsomeguy]

SELECT * FROM table WHERE col NOT REGEXP '[0-9]{4}'

[OldMan]

You might experiment with the use of _ rather than %. The underscore is supposed to represent one character while % is supposed to represent 0-to-many characters.

 

http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#function_strcmp

I tried:

 

select * from table WHERE col NOT LIKE '1%%%' AND col NOT LIKE '2%%%';

select * from table WHERE col NOT LIKE '%1___%' AND col NOT LIKE '%2___%';

return the same result

SELECT * FROM table WHERE col NOT REGEXP '[0-9]{4}'

 

Idk why, but don't show any result.

[justsomeguy]

 

 

Idk why, but don't show any result.

Sounds like there aren't any records that don't have a 4-digit number somewhere in them. When I run that on my database it works to exclude any record that has a sequence of 4 digits.

[OldMan]

Sounds like there aren't any records that don't have a 4-digit number somewhere in them. When I run that on my database it works to exclude any record that has a sequence of 4 digits.

yes, working...i tried in wrong column.

 

Thanks 4 all.