Query for dollar amts in text.

[abenitez77]

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%'

 

[SQLScholar]

abenitez77,

In terms of the system you have currently - yes i cant think of a better way.

If you have any control over the structure of the database - this really needs normalisation.

Starting point would be to put the numeric values into a diffent field (currency?).

Then i would make sure that the information you need about CA is stored in a field so that it is either yes or no - or even as text. As long as its always the same thing.

The difficulties you are having are from poor design. At the end of the day random entry into a database will end up with queries like the above to attempt to get the data out.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[markros]

If you want to get everything that starts with that word, then you need to USE LIKE 'CA%' (e.g. don't use % in front, as it's bad for performance).

I also suggest to put all possible variations into a table variable or temp table or better permanent lookup table in a database if you need to run such queries often

declare @Credit table (Keyword varchar(10))
insert into @Credit values ('CA '),('CA#'), etc.

SELECT T.fields from myTable T INNER JOIN @Credit C
ON T.MESSAGE LIKE C.Keyword + '%'

PluralSight Learning Library