Menu
I'm working on a project where we have to figure out if a given field is potentially a company name versus an address.
In taking a very broad swipe at it, we are going under the assumption that if this field contains no numbers, odds are it is a name vs. a street address (we're aiming for the 80% case, knowing some will have to be done manually).
So now to the question at hand. Given a table with, for the sake of simplicity, a single varchar(100) column, how could I find those records who have no numeric characters at any position within the field?
For example:
"Main Street, Suite 10A" --Do not return this.
"A++ Billing" --Should be returned
"XYZ Corporation" --Should be returned
"100 First Ave, Apt 20" --Should not be returned
Thanks in advance!
700
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.
As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.
LEN() Function in SQL Server LEN() function calculates the number of characters of an input string, excluding the trailing spaces. It is an expression that can be a constant, variable, or column of either character or binary data.
In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column. We can alternatively run a separate query to return all values that contain numeric data.
Sql Server allows for a regex-like syntax for range [0-9] or Set [0123456789] to be specified in a LIKE operator, which can be used with the any string wildcard (%). For example:
select * from Address where StreetAddress not like '%[0-9]%';
The wildcard % at the start of the like will obviously hurt performance (Scans are likely), but in your case this seems inevitable.
Another MSDN Reference.
191
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
