On 2017-04-07 17:29, David G. Johnston wrote:
> On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108
> <Dinesh.Chandra@cyient.com> wrote:
>
>> Dear Vinny,
>>
>> Thanks for your valuable replay.
>>
>> but I need a select query, which select only that record which
>> starts from IP "172.23.110" only from below table.
>>
>> xxx 172.23.110.175
>> yyy 172.23.110.178
>> zzz 172.23.110.177
>> aaa 172.23.110.176
>> bbb 172.23.111.180
>> ccc 172.23.115.26
>
> SELECT ... WHERE substring(ip_addr::text, 1, 10) = '172.23.110'
>
> David J.
>
While it's certainly possible to do it with a substring(), I'd strongly
advise against it,
for several reasons, but the main one is that it does not take into
account what happens to the presentation of the IP address when cast to
a string. There might be some conditions that cause it to render as
'172.023.110' instead of '172.23.110' just like numbers can be rendered
as '1.234,56' or '1,234.56' depending on locale, and that would break
the functionality without throwing an error.
Generally speaking; if you find yourself using a substring() on a
datatype other than a string,
you should check if there isn't an operator that already can do what you
want to do. PostgreSQL has operators
to do all the basic things with the datatypes it supports, so you don't
have to re-invent the wheel. :-)
