CODEWITHSUNDEEP
regexpostgresqlnumeric
MISMajorDeveloperAnyways
MISMajorDeveloperAnyways

Reputation: 1439

Extracting id number from a varchar in PostgreSQL

I have a field that contains mixed data with an id number that I want extract to another column. The column I wish to extract from has some records that match the format 'lastname, firstname-ID'. I only want to strip the 'ID' part, and from those columns who have a '-' and numbers following it.

So what I was trying to do was...

update data.xml_customerqueryrs
set new_id = regexp_replace(name, '[a-z]A-Z]', '')
where name like '%-%';

I know there is something minor that I need to fix, but I am not sure as the postgresql documentation for pattern matching doesn't really do a good job covering searching for only numerics.

Upvotes: 0

Views: 2811

Answers (2)

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Or you can also do:

new_id = substr(name, strpos(name, '-'), length(name))

Ref: Strings

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

If you actually only want to strip the 'ID' part:

new_id = regexp_replace(name, '-.*?$', '')

Or, if you, in fact, want to extract the ID part:

new_id = substring(name, '-(.*?)$')

I use the *? quantifier, so that only the last part is extracted, where a name has a - in it. Like:

Skeet-Gravell,John-1234

String functions in current manual

Upvotes: 3

Related Questions