How to use substring of result in PosrgreSQL/SQL

Question

I don't know how to name this syntax properly. I got a table (say T) with column A in which store the entries in format USER-YYYY-MMDD. I want to extract all rows whose A column's year part (YYYY part) is greater than 2010. E.g.

TABLE T
+----------------+
|        A       |
+----------------+
| USER-2011-1234*|
| USER-1992-1923 |
| USER-2014-1234*|
+----------------+

(*) are what I want: YYYY part is greater than 2010. SQL should looks like this, but I dont know how to say it in PostgreSQL.

 SELECT * FROM T WHERE A[5-8] > 2010

Thanks!

user330315 · Accepted Answer · 2014-03-24 08:56:17Z

3

select *
from t
where to_number(substr(a, 6, 4)) > 2010;

Note that his will fail with an error if the string cannot be converted to a number

More details in the manual: http://www.postgresql.org/docs/current/static/functions-string.html

Btw: storing more than one information in a single column is a bad design. You should store the username and the date in two different columns. Additionally storing dates as varchar is also a very bad idea. The date should be stored as date not as varchar

answered Mar 24, 2014 at 8:56

user330315

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

7 Comments

Hamlet Hakobyan Over a year ago

As a result of the problems mentioned in the post you have performance issue in your query as non-sargable predicate.

user330315 Over a year ago

@HamletHakobyan: as a workaround one, could create an index on to_number(substr(a, 6, 4))

Hamlet Hakobyan Over a year ago

Is the analogue of this in the mssql? Can you provide the link to documentation of this type of index. How it named? Index on expression?

Craig Ringer Over a year ago

@HamletHakobyan What's MS SQL got to do with this? In PostgreSQL, these are referred to as "functional indexes" or "expression indexes".

Hamlet Hakobyan Over a year ago

@CraigRinger MS SQL just forces to have well designed databases :)

|

Sathish · Accepted Answer · 2014-03-24 08:59:41Z

2

Try Like this

select *
from t
where  substring(a::text from 6 for 4)::integer > 2010;

answered Mar 24, 2014 at 8:59

Sathish

4,4854 gold badges36 silver badges60 bronze badges

1 Comment

qweruiop Over a year ago

It works. Thank you! Can you provide a link to the documentation for :: stuff?

Collectives™ on Stack Overflow

How to use substring of result in PosrgreSQL/SQL

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related