How to trim ONLY certain characters from string in Postgresql database?

Question

I have a table in a db that is labeled like so:

TableA:

  ID               ID2    Text
0 64556-546-1    465846    adfadf    
1 64556-546-1    465846    adfadf
2 64556-546-1    465846    adfadf

I want to trim the symbol(-) and digit after the dash only from the first column and only the dash in the second position how would I go about this? so the number will look like 64556-546.

The value will be compared to another in another table, so no need to create another column.

Thank you in advance.

juju · Accepted Answer · 2016-09-28 20:15:12Z

2

I'd check here;

https://www.postgresql.org/docs/9.1/static/functions-string.html

and do a;

regexp_replace(string text, pattern text, replacement text [, flags text])

So one way to do it (ignoring probable performance issues), would be;

SELECT TRIM(BOTH '-1' FROM regexp_replace(ID, '[-]', '' )) FROM TableA

But you could probably build a better way with a more advanced regex.

edited Sep 28, 2016 at 20:15

answered Sep 28, 2016 at 19:45

juju

5541 gold badge5 silver badges22 bronze badges

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

2 Comments

Abelisto Over a year ago

Hint: select substring('64556-546-1' from '[^-]+-[^-]+'); ;)

RustyShackleford Over a year ago

@Abelisto thank you for the helpful hint! and thank you juju for the reference!

Collectives™ on Stack Overflow

How to trim ONLY certain characters from string in Postgresql database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related