validate IP Address using SIMILAR TO

[Baron]

what is the best way to validate a string against IP Address?

I tried using this, it works.

select 'is valid ip address' where '192.168.0.11' similar to '[0-9]+.[0-9]+.[0-9]+.[0-9]+'

But I have problem with this

select 'is valid ip address' where '192.16845444.0.11' similar to '[0-9]+.[0-9]+.[0-9]+.[0-9]+'

[VolkerBarth · Accepted solution]

Here's an REGEXP sample for IP addresses from the docs – I have not tested it myself:

((2(5[0-5]|[0-4][0-9])|1([0-9][0-9])|([1-9][0-9])|[0-9])\\.){3}(2(5[0-5]|[0-4][0-9])|1([0-9][0-9])|([1-9][0-9])|[0-9])

You may also check general regex samples for IP addresses like these, just make sure the syntax is also supported by SQL Anywhere.

[Baron · Accepted solution]

thanks for the answer, actually it is not so simple having that the range should be between 0-255, so my approach above is not yet correct.

[Baron]

Oh, I found it:

select 'is valid ip address' where '192.168.0.1' similar to '[0-9]?[0-9]?[0-9]?.[0-9]?[0-9]?[0-9]?.[0-9]?[0-9]?[0-9]?.[0-9]?[0-9]?[0-9]?'

but is there another simpler one?

[Baron]

Is it also possible to change the above statement so that to reject numbers starting with 0?

For example, this should not be validated:

select 'is valid ip address' where '192.168.0.001' similar to '[0-9]?[0-9]?[0-9]?.[0-9]?[0-9]?[0-9]?.[0-9]?[0-9]?[0-9]?.[0-9]?[0-9]?[0-9]?'

[fvestjens]

This one should do the trick:

'((2(5[0-5]|[0-4][0-9])|1([0-9][0-9])|([1-9][0-9])|[0-9]).){3}(2(5[0-5]|[0-4][0-9])|1([0-9][0-9])|([1-9][0-9])|[0-9])'

[VolkerBarth]

Just to clarify: This is the same expression as the doc sample from my answer with the subtle distinction that is is meant for SIMILAR TO and there does not need to mask the dot whereas the expression from the sample is meant for REGEXP and therefore needs to mask the dot via '\\.' to prevent it from being treated as a meta character...

[fvestjens]

Correct. I referred to the example given by Baron.

By the way in the SQL Anywhere document portal the example for IP addresses in the Regular Expressions Examples is empty.

See https://help.sap.com/docs/SAP_SQL_Anywhere/93079d4ba8e44920ae63ffb4def91f5b/81729adf6ce210149807828f...

[jack_schueler]

I left a comment on that doc page mentioning the empty text.