CODEWITHSUNDEEP
mysqlsqlpostgresql
Nguyen Hoang Vu
Nguyen Hoang Vu

Reputation: 853

Familiar function of PATINDEX in mysql for Postgresql

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

Upvotes: 2

Views: 1882

Answers (2)

Shawn.X
Shawn.X

Reputation: 1353

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

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

Can you try POSITION() function:

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

Upvotes: 1

Related Questions