1

I have an interesting scenario to add - (minus sign) between numbers, alphabets and numbers.

Example:

Input: 123AB56; Output: 123-AB-56;

Input: 56XYZ777; Output: 56-XYZ-777;

There is no regular pattern for the source data.

Any inputs on this is appreciated.

Rob Paller
  • 7,506
  • 23
  • 23
user1472166
  • 11
  • 1
  • You have to try something then ask for question when you have a problem. `We feel the best Stack Overflow questions have a bit of source code in them, but if your question generally covers …` See http://stackoverflow.com/faq – Luc M Jun 21 '12 at 13:18
  • Not very easily on any release of Teradata prior to 14. You will need a UDF to do it effectively. With Teradata 14 use their native regular expression support. – Rob Paller Jun 21 '12 at 19:01

1 Answers1

3

If it's a relatively later Oracle, there is regexp_replace for these kind of tasks:

SELECT
  REGEXP_REPLACE(YOURFIELD,
                 '([[:digit:]]+)([[:alpha:]]+)([[:digit:]]+)',
                 '\1-\2-\3') REPLACED_RESULTS
  FROM YOURTABLE;
Zsolt Botykai
  • 46,263
  • 14
  • 81
  • 102