Find out the Position of Alphabets in a string.

[Verti]

Hi ,

Is there any way in Vertica to find out Alphabets starting and ending position in a string. I m getting some Zunk characters (,./&*....etc) at beginning and ending of string and need to clean the data.

Example: Sample data below

ID
,,,,..ABCXZY/,..
,,,,..ABC_XZY/,..
..ABCXZY..

I need to find out position of A and Position of Y in above strings. So that I wanted to remove the Zunk characters (,;/ .....etc) appearing at the beginning and end of the string and load only ABCXZY , ABC_XZY , ABCXZY from above example.

Thanks in Advance!

-Verti

[doug_harmon]

Check out Vertica's REGEXP functions.

http://my.vertica.com/docs/7.1.x/HTML/i ... s%7C_____0

REGEXP_INSTR finds the starting and ending positions of a string
REGEXP_SUBSTR allow you to extract a substring that matches specific criteria.

[JimKnicely]

See http://www.vertica-forums.com/viewtopic ... 1988#p6681. Maybe this will help.

[Verti]

Thanks so much, this solved my problem!

Jim, could you pls explain what exactly the 2nd part of the code doing, I mean after + symbol i.e. +|[^a-zA-Z0-9\\s]+$'

[NorbertKrupa]

That's called a regular expression.

[JimKnicely]

By the way, there is a cleaner regular expression my friend recommended that'll do the same thing:

Example:

Code: Select all

dbadmin=> select col1, regexp_replace(col1, '[^\w]+') from jim;
       col1        | regexp_replace
-------------------+----------------
 ,,,,..ABC_XZY/,.. | ABC_XZY
 ,,,,..ABCXZY/,..  | ABCXZY
 ..ABCXZY..        | ABCXZY
(3 rows)

[Verti]

Thanks a Ton.