SQL for comparison of strings comprised of number and text

Question

I need to compare 2 strings that contains number and possibly text. for example I have this table:

id | label 1  |  label 2 | 
1  |  12/H    |     1    |
2  |   4/A    |    41/D  |
3  |  13/A    |     3/F  |
4  |   8/A    |     8/B  |
..

I need to determine the direction so that if Label 1 < Label2 then Direction is W (with) else it is A (against). So I have to build a view that presents data this way:

id | Direction  
1  |     A    |   
2  |     W    |
3  |     A    |
4  |     W    |
..

I'm using postgres 9.2.

Erwin Brandstetter · Accepted Answer · 2013-01-09T22:52:24.087

3

WITH x AS (
   SELECT id
         ,split_part(label1, '/', 1)::int AS l1_nr
         ,split_part(label1, '/', 2)      AS l1_txt
         ,split_part(label2, '/', 1)::int AS l2_nr
         ,split_part(label2, '/', 2)      AS l2_txt
   FROM t
   )
SELECT id
      ,CASE WHEN (l1_nr, l1_txt) < (l2_nr, l2_txt)
       THEN 'W' ELSE 'A' END AS direction
FROM   x;

I split the two parts with split_part() and check with an ad-hoc row type to check which label is bigger.

The cases where both labels are equal or where either one is NULL have not been defined.

The CTE is not necessary, it's just to make it easier to read.

-> sqlfiddle

edited Jan 09 '13 at 22:52

answered Jan 09 '13 at 22:44

Erwin Brandstetter

605,456
145
1,078
1,228

1

To get the A and W, change the 2nd to last line SELECT id, case when (l1_nr, l1_txt) < (l2_nr, l2_txt) then 'W' else 'A' end AS direction – Gary - Stand with Ukraine Jan 09 '13 at 22:47
@Gary: Right, I added that, too. – Erwin Brandstetter Jan 09 '13 at 22:52
You may also be interested in this [closely related question by @Craig with a number of solutions for sorting of mixed number-and-text strings](http://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings). – Erwin Brandstetter Jan 09 '13 at 23:00

score 0 · Answer 2 · answered Jan 09 '13 at 22:43

You can try something like:

SELECT id, CASE WHEN regexp_replace(label_1,'[^0-9]','','g')::numeric <
                     regexp_replace(label_2,'[^0-9]','','g')::numeric
                THEN 'W'
                ELSE 'A'
           END
FROM table1

regexp_replace deletes all non numeric characters from the string ::numeric converts the string to numeric.

Details here: regexp_replace, pattern matching, CASE WHEN

SQL for comparison of strings comprised of number and text

2 Answers2