0

Does anyone have any ideas on how to split a string that has both Alpha numeric characters and letters?

For example UP111770 exists in one column but I need to split the column to show UP in one and the numbers in the other.

Then I will need to remove any leading zeros (if any exist) on the right side.

After that I need to concat them back together, but if there is a way to do this without splitting then that would be great.

Thank you!

Shaun Hurley
  • 1
  • Does this answer your question? [Reverse only numerical parts of string in sql server](https://stackoverflow.com/questions/30592050/reverse-only-numerical-parts-of-string-in-sql-server) – Shmiel Jun 23 '22 at 13:51
  • If it doesn't answer your question. Just [search here](https://stackoverflow.com/search?q=find+numbers+in+string+sql) and you'll find so many questions on this issue. – Shmiel Jun 23 '22 at 13:53
  • [REGEXP_EXTRACT](https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/impala_string_functions.html#string_functions__regexp_extract). – user14063792468 Jun 23 '22 at 14:01

1 Answers1

0

I think you can do this using below approach.

  1. remove leading 0s. REGEXP_REPLACE(mycolumn, "^0+", '')
  2. split numeric and alphabets separately.
    alphabets - REGEXP_REPLACE(mycolumn, "[0-9]", '')
    numeric - REGEXP_REPLACE(mycolumn, "[A-Za-z]", '')

Combine both to get a SQL -

select 
REGEXP_REPLACE(REGEXP_REPLACE(mycolumn, "^0+", ''), "[0-9]", '') myalphabets,
REGEXP_REPLACE(REGEXP_REPLACE(mycolumn, "^0+", ''), "[A-Za-z]", '') mynumerics
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33