Need help with postsql query field matching a certain domain names in the end as per below in a particular FIELD. 1234.abc.xyz.com; 0971.abc.xyz.com

WHERE CAST (domain_name AS text) LIKE  '%\d{4}.abc.xyz.com%'

#where domain_name is the FIELD name

CodePudding user response：

~ is used for regular expression matching, LIKE for simple matching. Read more about them here: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP

If you just want to find domain_name that end in a particular text, the simple matching works fine (don't know if you really need the cast):

select * from tbl_test where domain_name LIKE '%.abc.xyz.com'

This will not work correctly:

select * from tbl_test where domain_name ~ '\d\d\d\d.abc.xyz.com'

The dot (.) is "any character" in a regular expression so this domain would be selected: abcd.abcxxyzdcom. You need to escape the dot in the string for it to be treated literally like this: '\d\d\d\d\.abc\.xyz\.com'

Underscore is a wildcard for "any character" in the simple LIKE.

CodePudding user response：

use ~ followed by your search pattern as regular expression:

where domain_name ~ '\d\d\d\d\.abc\.xyz\.com' 

playground: https://dbfiddle.uk/O0Q_Ctmo

create table tbl_test (
  domain_name varchar
);


insert into tbl_test VALUES 
('1234.abc.xyz.com'),
('0971.abc.xyz.com'),
('0971.abc.xyz.bam'),
('1234.xxx.xyz.com'),
('123.xxx.xyz.com'),
('aaaa.xxx.xyz.com')

CREATE TABLE

INSERT 0 6

select * from tbl_test
where domain_name ~ '\d\d\d\d\.abc\.xyz\.com'

SELECT 2

fiddle