April 11, 2011 at 1:25 pm
I am querying a varchar field that has textual content with numberic information. I want to query for dollar ammounts in my database. Is this correct, or would you suggest I do it differently?
i.e.:
1,000
1000.00
$1000
$ 1000
1k
1m
Flat Dollars:
[MESSAGE] LIKE '%[0-9],[0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9][0-9][0-9][0-9].[0-9][0-9]%'
OR [MESSAGE] LIKE '%$[0-9][0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%$ [0-9][0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9]k%'
OR [MESSAGE] LIKE '%[0-9]m%'
In this example I want to query for anything that starts with CA or Credit Allowance.
CA words:
[MESSAGE] LIKE '%CA %'
OR [MESSAGE] LIKE '%CA#%'
OR [MESSAGE] LIKE '%CAS %'
OR [MESSAGE] LIKE '%CAS#%'
OR [MESSAGE] LIKE '%CREDIT ALLOW%'
OR [MESSAGE] LIKE '%CREDIT ALW%'
OR [MESSAGE] LIKE '%CREDIT ALLW%'
April 11, 2011 at 1:55 pm
GrassHopper (4/11/2011)
Flat Dollars:[MESSAGE] LIKE '%[0-9],[0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9][0-9][0-9][0-9].[0-9][0-9]%'
OR [MESSAGE] LIKE '%$[0-9][0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%$ [0-9][0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9]k%'
OR [MESSAGE] LIKE '%[0-9]m%'
I would consolidate the flat dollars statement to the following:
[MESSAGE] LIKE '%[0-9],[0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9].[0-9][0-9]%'
OR [MESSAGE] LIKE '%$%[0-9]%'
OR [MESSAGE] LIKE '%[0-9][km]%'
Perhaps the isnumeric function is an alternative to try.
In this example I want to query for anything that starts with CA or Credit Allowance.
CA words:
[MESSAGE] LIKE '%CA %'
OR [MESSAGE] LIKE '%CA#%'
OR [MESSAGE] LIKE '%CAS %'
OR [MESSAGE] LIKE '%CAS#%'
OR [MESSAGE] LIKE '%CREDIT ALLOW%'
OR [MESSAGE] LIKE '%CREDIT ALW%'
OR [MESSAGE] LIKE '%CREDIT ALLW%'
This part is ok. 🙂
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 11, 2011 at 3:23 pm
This line below would not work for us because we want only amounts 1,000 or greater.
OR [MESSAGE] LIKE '%[0-9].[0-9][0-9]%'
This line would be too broad, it would return things like "We need more ad $’s. Write CA 12345.”
OR [MESSAGE] LIKE '%$%[0-9]%'
April 12, 2011 at 12:39 pm
Ok,
if you want to be sure that there is a dollar amount in it you should exclude rows without dollar amount with the following:
[MESSAGE] NOT LIKE '%[%!"§&/()=?\@{[]}+*#_;a-jln-z]%' AND
( [MESSAGE] LIKE '%[0-9],[0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9][0-9][0-9][0-9].[0-9][0-9]%'
OR [MESSAGE] LIKE '%$[0-9][0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%$ [0-9][0-9][0-9][0-9]%'
OR [MESSAGE] LIKE '%[0-9][km]%'
)
Perhaps you want to add some other characters to the NOT LIKE-Condition.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply