Question 114030 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgressql query matching a field containing domainname

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

Tags:

2

Answers


  1. 0

    use ~ followed by your search pattern as regular expression:

    where domain_name ~ 'dddd.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 ~ 'dddd.abc.xyz.com'
    domain_name
    1234.abc.xyz.com
    0971.abc.xyz.com 
    SELECT 2

    fiddle

    Login or Signup to reply.
  2. 0

    ~ 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 ~ 'dddd.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: ‘dddd.abc.xyz.com’

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search