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
Find out the Position of Alphabets in a string.
Moderator: NorbertKrupa
-
- Beginner
- Posts: 36
- Joined: Fri Feb 17, 2012 6:09 pm
- Contact:
Re: Find out the Position of Alphabets in a string.
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.
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Find out the Position of Alphabets in a string.
See http://www.vertica-forums.com/viewtopic ... 1988#p6681. Maybe this will help.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Find out the Position of Alphabets in a string.
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]+$'
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]+$'
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Find out the Position of Alphabets in a string.
That's called a regular expression.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Find out the Position of Alphabets in a string.
By the way, there is a cleaner regular expression my friend recommended that'll do the same thing:
Example:
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Find out the Position of Alphabets in a string.
Thanks a Ton.