This is based on a question previously asked which was deleted by the OP but it got me thinking and playing round with it, and I managed to do it without using a regex. The question is also a lesson in the perils of allowing users to enter free text!
However, my solution is quite cumbersome and I’ve wracked my brains to see if I could find a more elegant solution, but no joy.
So, I’m going to offer a bonus of 100 points to the person who can provide one. I’m not going to put my own solution forward yet.
A typical string might be like this:
'First order 437.3/10-87 16NY100013XX55 - Return'
So, the only thing you can be sure of is that the string (order_name
) starts with '437.'
and that it ends with the last digit in the string. The text following the order is arbitrary: its only constraint is that it can’t contain a digit!
So, the result for this particular example would be
'437.3/10-87 16NY100013XX55'
So, my table and data are below and also on the fiddle here:
CREATE TABLE t1
(
id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
order_name TEXT NOT NULL
);
strings:
INSERT INTO t1 (order_name) VALUES
('First order 437.8/03-87 22LA190028YV7ER55'),
('Second order 437.8-03-87 22LA19'),
('First order 437.3/10-87 16NY100013XX55 - Return'),
('Order 21.02.2022 437.8/10-87 16WA239766'),
('437.8/10-87 16NY10023456YY78 - Paid'),
('First order (437.8/03-87 22LA190028)'),
('Visit 02.02.2023 Order 437.5/10-87 16DC107765X56 REFUND'),
('Visit 02.02.2023 Order 437.5/10-87 16DC1077657FFR56REFUND'),
('Visit 02.02.2023 Order 437.5/10-87 16DC107765745 - Reorder');
Desired result:
Order Text
437.8/03-87 22LA190028YV7ER55
437.8-03-87 22LA19
437.3/10-87 16NY100013XX55
437.8/10-87 16WA239766
437.8/10-87 16NY10023456YY78
437.8/03-87 22LA190028
437.5/10-87 16DC107765X56
437.5/10-87 16DC1077657FFR56
437.5/10-87 16DC107765745
The fiddle is PostgreSQL 16, but I’ve checked and it works back to version 10. The question is PostgreSQL based, but elegant solutions from other systems will also get an upvote.
Please let me know if any further information is required.
4
Answers
EDIT: Originally I was looking for the first digit after the word "Order "because I missed the part where the Order starts with "437". I’ve updated the answer but left the slightly more complex logic of finding the start position of the search
This is a TSQL solution
position is calculated by reversing the string
CROSS APPLY
withVALUES
so I can refer to the previous calculations without repeating the code (for readability)The only tricky thing should be off-by-one errors
Result
In order to extract string without using regular expressions, I would use string functions like POSITION, and SUBSTRING. I have two solutions:
Hope my solutions are aligned with yours, and this is what you were looking for.
Find the positions of ‘437.’ and the last digit in the
order_name
string (with a scalar subquery) to use withsubstring
function.Fiddle
Just selecting the substring starting at the position of
437.
, and ending on the second space after the start of that:output:
see: DBFIDDLE
EDIT: Removing the
REFUND
, or the)
, using regular expressions is easy, just to:When doing this without regular expression, might need a user defined function, especially when you do not want to hard code stuff like
REFUND
orREORDER
see: DBFIDDLE
EDIT2: To Remove letters, and other non-numeric characters, from the right side of the string, I created this function:
This can be used as
SELECT trimlettersfromrightofstring('1234abcdef5hijk');
, which returns1234abcdef5
This is the link to the adapted DBFIDDLE, which produced the desired results.