query for postgresql - Extract the number on the title and order by number

[Alex_isback]

Hi everyone,

this is my first post

I state that I am not a programmer, only passion and desire to learn. I would need help, PostGresql and PHP use.

In this query I should extract the number from the title, after asterisk #

Quote

"The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)}
"The Bionic Woman" (1976) {Jaime's Mother (#1.8)}
"The Bionic Woman" (1976) {Rancho Outcast (#3.21)}

 

SELECT
	movies.movieid,
	movies.title,
	movies.year,
	movies.imdbid,
	CAST ( SUBSTRING ( movies.title FROM '#(\d+\.\d+)' ) AS DECIMAL ) AS numero // 
FROM
	movies 
WHERE
	movies.title ILIKE ('%$risultato%') 
ORDER BY
	numero ASC

I use Cast and Decimal (perhaps it is wrong) and Substring to extract the number, and is extracted and put in a field of name number (numero)

The problem is to order everything in natural order

example:

1.1

1.2

1.9

etc..

1.10

etc..

This is the result of the query, without ordering in the number (numero) field

 

 

 

 

[Barand]

The obvious question is "Why not store the #x.xx in a separate column?". But given where you are...

SELECT title FROM movie;
+-----------------------------------------------------------+
| title                                                     |
+-----------------------------------------------------------+
| "The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)} |
| "The Bionic Woman" (1976) {Jaime's Mother (#1.8)}          |
| "The Bionic Woman" (1976) {Rancho Outcast (#3.21)}         |
+-----------------------------------------------------------+

SELECT title FROM movie
  ORDER BY SUBSTRING_INDEX(title, '#', -1)+0;
+-----------------------------------------------------------+
| title                                                     |
+-----------------------------------------------------------+
| "The Bionic Woman" (1976) {Jaime's Mother (#1.8)}          |
| "The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)} |
| "The Bionic Woman" (1976) {Rancho Outcast (#3.21)}         |
+-----------------------------------------------------------+

 

[DavidAM]

I don't know Postgresql. The problem you're going to have is that 1.10 is less than 1.9, In fact, it is equal to 1.1.

If the Cast-Substribg works (again, I don't know Postgres), you should do it twice to split the version number. And order by the two values. Something like this

CAST ( SUBSTRING ( movies.title FROM '#(\d+)\.\d+' ) AS INT ) AS major, 
CAST ( SUBSTRING ( movies.title FROM '#\d+\.(\d+)' ) AS INT ) AS minor

...

ORDER BY major, minor

You can use Integer now, instead of Decimal.

[Barand]

12 minutes ago, DavidAM said:

The problem you're going to have is that 1.10 is less than 1.9,

Not with my method, which sorts them numerically.

Original order

+-----------------------------------------------------------+
| title                                                     |
+-----------------------------------------------------------+
| The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)} |
| The Bionic Woman" (1976) {Jaime's Mother (#1.8)}          |
| The Bionic Woman" (1976) {Rancho Outcast (#2.5)}          |
+-----------------------------------------------------------+

Sorted (...ORDER BY SUBSTRING_INDEX(title, '#', -1)+0)

+-----------------------------------------------------------+
| title                                                     |
+-----------------------------------------------------------+
| The Bionic Woman" (1976) {Jaime's Mother (#1.8)}          |
| The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)} |
| The Bionic Woman" (1976) {Rancho Outcast (#2.5)}          |
+-----------------------------------------------------------+

 

[Alex_isback]

2 hours ago, Barand said:

The obvious question is "Why not store the #x.xx in a separate column?". But given where you are...

SELECT title FROM movie;
+-----------------------------------------------------------+
| title                                                     |
+-----------------------------------------------------------+
| "The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)} |
| "The Bionic Woman" (1976) {Jaime's Mother (#1.8)}          |
| "The Bionic Woman" (1976) {Rancho Outcast (#3.21)}         |
+-----------------------------------------------------------+

SELECT title FROM movie
  ORDER BY SUBSTRING_INDEX(title, '#', -1)+0;
+-----------------------------------------------------------+
| title                                                     |
+-----------------------------------------------------------+
| "The Bionic Woman" (1976) {Jaime's Mother (#1.8)}          |
| "The Bionic Woman" (1976) {Jaime's Shield: Part 2 (#2.11)} |
| "The Bionic Woman" (1976) {Rancho Outcast (#3.21)}         |
+-----------------------------------------------------------+

 

Warning: pg_query(): Query failed: ERROR:The substring_index function (Character Varying, Unknown, Integer) does not exist line 19: Order by Substring_index (title, '#', -1) +0; ^ Hint: no function found with name and types of topics provided

[Alex_isback]

1 hour ago, DavidAM said:

I don't know Postgresql. The problem you're going to have is that 1.10 is less than 1.9, In fact, it is equal to 1.1.

If the Cast-Substribg works (again, I don't know Postgres), you should do it twice to split the version number. And order by the two values. Something like this

CAST ( SUBSTRING ( movies.title FROM '#(\d+)\.\d+' ) AS INT ) AS major, 
CAST ( SUBSTRING ( movies.title FROM '#\d+\.(\d+)' ) AS INT ) AS minor

...

ORDER BY major, minor

You can use Integer now, instead of Decimal.

this?

<td><?php echo $row->major.'.'.$row->minor?></td>

How do I unite the two Row values?

[Alex_isback]

😐

 

[DavidAM]

1 hour ago, Alex_isback said:

this?

<td><?php echo $row->major.'.'.$row->minor?></td>

How do I unite the two Row values?

I would think that would work. Make sure you are casting to an numeric value (Integer) so the Order by is sorting numerically, not be string values.

[Alex_isback]

6 hours ago, DavidAM said:

I would think that would work. Make sure you are casting to an numeric value (Integer) so the Order by is sorting numerically, not be string values.

It only works with this trick

 

SELECT
	movies.movieid,
	movies.title,
	movies.year,
	movies.imdbid,
	CAST ( SUBSTRING ( movies.title FROM '#(\d+)\.\d+' ) AS INT ) AS major, 
    CAST ( SUBSTRING ( movies.title FROM '#\d+\.(\d+)' ) AS INT ) AS minor


FROM
	movies 
WHERE
	movies.title ILIKE ('%$risultato%') 

ORDER BY major, minor

and inserting this without any reference with the field (number, it does not exist in the query)

<td><?php echo $row->numero; ?></td>

in datatables

"order": [[ 3, "asc" ]],

Only in this way orders correctly,

But I don't understand the logic, since the number does not exist 🙄

anyway thanks 😉

result

 

 

 

Edited December 2, 2022 by Alex_isback