Familiar function of PATINDEX in mysql for Postgresql

Question

Is there any familiar function for PATINDEX of mysql for postgresql. I'm trying to make a sql like this in postgres.

SELECT PATINDEX('%schools%', 'W3Schools.com');

which throw an error:

no function matches the given name and argument types. you might need to add explicit type casts

To be more detailed, I'm trying to get seperate number part and string part of a string in Postgresql. I found example like this:

SELECT Section
FROM dbo.Section
ORDER BY LEFT(Section, PATINDEX('%[0-9]%', Section)-1), -- alphabetical sort
         CONVERT(INT, SUBSTRING(Section, PATINDEX('%[0-9]%', Section), LEN(Section))) -- numerical

Shawn.X · Accepted Answer · 2019-06-12 08:41:28Z

2

There are two ways to implement this, the example as below:

postgres=# select strpos('W3Schools.com','Schools');
 strpos 
--------
      3
(1 row)

postgres=# select position('Schools' in 'W3Schools.com');
 position 
----------
        3
(1 row)

postgres=# select regexp_matches('hahahabc123zzz', '(abc)(123)');
 regexp_matches 
----------------
 {abc,123}

postgres=# select array_to_string(regexp_matches('hahahabc123zzz', '(abc)(123)'),' ');
 array_to_string 
-----------------
 abc 123

postgres=# select (regexp_matches('hahahabc123zzz', '(abc)(123)'))[1] as a, (regexp_matches('hahahabc123zzz', '(abc)(123)'))[2] as b;
  a  |  b  
-----+-----
 abc | 123
(1 row)

Do you want this? And you can get all functions of string process here: https://www.postgresql.org/docs/10/functions-string.html

edited Jun 12, 2019 at 8:41

answered Jun 12, 2019 at 8:24

Shawn.X

1,3637 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nguyen Hoang Vu Over a year ago

I'm sorry but I edited the question. can you please give another answer

Shawn.X Over a year ago

@NguyenHoangVu-K11FUGHCM So you want to get a substring?Can you give some example datas?

Nguyen Hoang Vu Over a year ago

abc123, I need to seperate 'abc' and '123'. Thanks

Nguyen Hoang Vu Over a year ago

do you have a regex for string path? I found one for numeric path (\d+)

Nguyen Hoang Vu Over a year ago

"abc123" is just an example ^^ but thanks for your help with regexp_matches. I'm working on it

|

Arulkumar · Accepted Answer · 2019-06-12 08:28:17Z

1

Can you try POSITION() function:

SELECT POSITION('schools' in 'W3Schools.com');

answered Jun 12, 2019 at 8:28

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

2 Comments

Nguyen Hoang Vu Over a year ago

I'm sorry but I edited the question. can you please give another answer

Arulkumar Over a year ago

@NguyenHoangVu-K11FUGHCM I hope you need regexp_matches()

Collectives™ on Stack Overflow

Familiar function of PATINDEX in mysql for Postgresql

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related