Search code examples
sqlregeximpalahue

Extract first few alphabets before numbers and skip the rest

I have a strange requirement where we want to extract first few alphabets in a string and skip the rest where there can be combination of alphabets and numbers. e.g.
Text

1.  aaaa bbbb ccc 00110011 ddd eee ff 00 99 
2.  bbbbbbbbbbbbbbbbbbbbbb 0123 azbe 999999999999999  
3.  zfsfzzzzzzzzzz asasas 111 3333333 xxysys ddddd  9090909  
4.  Returns the nth field within a delimited string 5555555555 99999999

Output 

1.  aaaa bbbb ccc 
2.  bbbbbbbbbbbbbbbbbbbbbb 
3.  zfsfzzzzzzzzzz asasas 
4.  Returns the nth field within a delimited string

We are using Hue/Impala and would like to extract all alphabets in first characters or all left characters before a number string starts.

Thank you

Solution

  • I think regexp_extract() should work:

    select regexp_extract(col, '^[a-zA-Z ]+', 0)