February 1, 2017 at 3:45 am
Firstly, I'm not sure the subject clearly explains what I am trying to do.
Basically, I have an Infopath form that submits to a SQL table. In the field for customer name, in error, there have been entries that are not the customer name. In some cases, they are the customer's contact details and/or other numeric entries.
I have rectified the issue in that I have placed a data validation rule in the Infopath form so this won't happen again.
However I need to rectify all the previous entries.
I am unsure of how to return all the results where a numeric entry was made into the customer name field as opposed to an alphabetic entry.
I am struggling to define the where clause.
select*
from
[table_name]
WHERE CUSTOMER_NAME not LIKE '%[^0-9]%-%'
This returns both the alphabetic and numeric entries.
I only want the numeric entries
thanks in advance
February 1, 2017 at 4:18 am
Something like this?
DECLARE @john-2 table (CustName varchar(10))
INSERT INTO @john-2 VALUES ('John'),('10234'),('John22'),('22John'),('123-456')
SELECT CustName
FROM @john-2
WHERE CustName NOT LIKE '%[^0-9\-]%' ESCAPE '\'
Edit - make sure you choose as your escape character a character that doesn't appear in the column you're searching.
John
February 1, 2017 at 4:24 am
Are you simply trying to get entries that have a number in them?
This should therefore work:SELECT *
FROM [table_name]
WHERE CUSTOMER_NAME LIKE '%[0-9]%';
If you want to return value where the name doesn't contain characters you would expect in a name (lets same letters, spaces, hyphens and apostrophes), then you could use:SELECT *
FROM [table_name]
WHERE CUSTOMER_NAME LIKE '%[^a-zA-Z'' -]%';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 1, 2017 at 5:50 am
Thanks guys!
Both suggestions have been insightful!
thanks again for the help
always appreciated
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply