I have a string like 'ABC3245-bG1353BcvG34'. I need to remove the hyphen including all the letters after hyphen.
so the above string should be ABC3245135334
I tried the below code:
select substring('ABC3245-bG1353BcvG34' from 1 for (position('-' in 'ABC3245-bG1353BcvG34')-1))||regexp_replace(substring('ABC3245-bG1353BcvG34' from (position('-' in 'ABC3245-bG1353BcvG34') +1) for length('ABC3245-bG1353BcvG34')),'[a-zA-Z]','')
but not able to remove letters after the hyphen.
I need to remove the hyphen including all the letters after hyphen.
so the above string (
ABC3245-bG1353BcvG34) should beABC3245135334
This suggests that all numbers should remain after the hyphen (in their original order). If that's what you want, you cannot do this with a single regex. Assuming you can have only 1 hyphen in your input:
SELECT substring(input.value from '^[^-]*') ||
regexp_replace(substring(input.value from '[^-]*$'), '\D+', '', 'g')
FROM (SELECT 'ABC3245-bG1353BcvG34'::text AS value) AS input
If you can have multiple hyphens in your input, please define how to handle characters between hyphens.
SELECT a[1] || regexp_replace(a[2], '\D', '', 'g')
FROM string_to_array('ABC3245-bG1353BcvG34', '-') AS a
Or, more convenient to deal with a set (like a table):
SELECT split_part(str, '-', 1)
|| regexp_replace(split_part(str, '-', 2), '\D', '', 'g')
FROM (SELECT 'ABC3245-bG1353BcvG34'::text AS str) tbl
Removes all non-digits after the hyphen. (Assuming there is only one hyphen.) Result:
ABC3245135334
Missed that OP wants to remove all letters after -.
SELECT regexp_replace('ABC3245-bG1353BcvG34', '-\D*', '')
Result:
ABC32451353BcvG34
Regex explained:
- .. literal hyphen -
\D .. class shorthand for "non-digits".
* .. 0 or more times
Removes the first hyphen and everything that follows until the first digit.
ABC3245135334 as in the question. I need to remove the hyphen including all the letters after hyphen. The question suggests that all numbers should remain after the hyphen (in their original order).
A RegEx that would work:
[a-zA-Z0-9]+(?=-)
Do note that this requires the string to actually contain the hyphen. It uses a lookahead to grab a substring of all alphanumeric characters followed by a hyphen.
