Get substring form an integer column

Question

I had a quick question, how can I go about using SUBSTRING on an integer? I currently have field labeled "StoreID" that contains a 5 digit integer (60008). I am trying to use SUBSTRING to remove the 6 when I query out this information. When I use something like:

SUBSTRING('StoreID', 2, 6) I get an error returning back saying that SUBSTRING(integer,integer,integer) does not exist.

Is there another function I can use in postgres to accomplish what I am trying to do?

jhole · Accepted Answer · 2013-05-20 15:38:23Z

8

You can cast the integer

SUBSTR(cast (StoreId as text), 2,6)

answered May 20, 2013 at 15:38

jhole

4,2712 gold badges24 silver badges38 bronze badges

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

Comments

Erwin Brandstetter · Accepted Answer · 2023-02-11 13:20:54Z

5

If you are interested in the number 8, use the modulo operator %

SELECT 60008%10000

If you want the string '0008', the function right() is right for you (added with Postgres 9.1):

SELECT right(60008::text, -1)

Or with modulo and to_char():

SELECT to_char(60008%10000, 'FM0000')

The FM modifier removes space padding.

edited Feb 11, 2023 at 13:20

answered May 20, 2013 at 17:55

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Get substring form an integer column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related