Menu
I have a table with an address street, I want to make sure the address contains the street as well as house/building number (saxon 17) I want to write an SQL query to find rows where field address does not contain a numeric value. How can I do this?
615
This is the way: SELECT * FROM TABLE_NAME WHERE NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$ '); This also excludes values which contain decimals.
SQL Server has an ISNUMERIC() function that returns 1 for numeric values and 0 for non-numeric values.
Method 1 - Using CHARINDEX() function This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero). Let us understand this with examples.
The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
This can be done using a regular expression:
select *
from the_table
where address_column !~ '[0-9]'
More details about regular expressions and pattern matching can be found in the manual:
http://www.postgresql.org/docs/current/static/functions-matching.html
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
