So basically I have a field that is 6 numbers, so for example 298531...i want to filter based upon the first 4 numbers 2985 of that six digit value within a field. And then I may eventually want to only filter on the first 2.

Select *
FROM table
WHERE
sixdigitnumberfield LIKE '2985%' (wouldn't work with numbers right?)


How to do this with a field that is numbers instead of text? Truncate command? Anyone know?

should work. you may have to cast it to a string/varchar/text/etc first.

divide by 100?

select ... where sixdigitnumber / 100 = 2985

although that is a pretty clever work around ..there must be a more legitimate way to do it , no?

I will use that as a plan b for sure though.

did you try casting it like i suggested?

It is the best way, converting to varchar to take only 4 first chars and converting back to int has big performance penalty.

Something like

select .. where sixdigitnumber::varchar like '2985%';

will also work but is much uglier. Look:



vs



This post was edited by nuvo on Mar 15 2015 09:46am

awesome...incredibly helpful post. can't wait to give it a try in a bit.

One thing I forgot to mention though is that the reason I'm using the first 4 digits, is that this is for an industry code. Industry codes get more specific with every digit. I'm limiting it to 4 because I'd be excluding too much data otherwise. To give you an idea 4 digits might be restaurants (the data set I want) where six digits might be restaurants that specialize in sushi or a specific type of cuisine.

where sixdigitnumber::varchar like '2985%' may actually be more applicable. Can I leave it at that or will I need to convert it back to an integer afterwards? This is all rather new to me, and i don't want to ruin the original data set within the table.

This post was edited by noflexzone on Mar 15 2015 09:56am

You won't ruin anything with simple selects, it doesn't change anything in db. I just realized I was silly when I said about converting, query returns original data not the where clause anyway you can leave it that way. Note that this example was for postgresql, if you use different database typecast may look different.

Both solutions - typecast to varchar and dividing by 100 - return the exact same data. But the LIKE function is just slow


This post was edited by nuvo on Mar 15 2015 10:03am