Checking for regular expressions in a string field

Hi:

   I was wondering if there is a way to check for regular expressions in DB2's SQL. For instance, given a string field of value "Mary";"Tom";"Bob" I would like to be able to chack all are seperated by semi colons and have a double quote text qualifier. Any idea?

Thanks
LuckyLucksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you can not do that using a regular expression
you can try doing it using like predicate, but it's far less powerfull than regular expression
try doing
where x like '"%";"%";"%"'
0
LuckyLucksAuthor Commented:
and what if there are more elements than the three names like unknown number of names at the time of writing the SQL?
0
Kent OlsenDBACommented:
Hi LuckyLucks,

You can do what you've asked, but "pure" SQL can get very, very ugly.

You've got three "real" choices.

1.  Code recursive SQL to process everything delimited by ";".
2.  Write a stored procedure to parse the string in SQL.
3.  Write C code to parse the string.  The C code can be implemented as a stored procedure.


I'll be glad to help,
Kent
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

ghp7000Commented:
i think you can use the posstr function and the locate function to answer your question.
Not very elegent solution but here goes:
First, find the position of the , in all of your rows:
select DISTINCT posstr(column_name, ',') from table
this returns the FIRST OCCURENCE of the comma
next
select select DISTINCT posstr(column_name, ',') from table where posstr > (value returned from first query)
continue on until you have met or exceeded the columns length
Now you know where the commas are located in the field
Now you can use the locate function to test to see if there is " before the comma
select locate (' " ', column_name) from table where posstr(column_name,',')=position of commas from aboev queries



0
Kent OlsenDBACommented:
Hi Lucky,

Just in case you're still watching this thread, please read the article here:

  http://www-128.ibm.com/developerworks/db2/library/techarticle/0303stolze/0303stolze1.html

It describes the recursive SQL required to do what you want.  In fact, the example on that page parses a comma delimited string.  (You'll probably want to use '","' -- double-quote, comma, double-quote -- for your delimiter so that quoted strings can contain commas.


Kent
0
LuckyLucksAuthor Commented:
thanks for the post guys, however, I would need the code to award full points.
Kdo, the link talks abt displaying elements, however I am checking for the negative case, can't eyeball the list of returns to see if they are correct.
0
Kent OlsenDBACommented:
Hi LuckyLucks,

That's the easy part.  :)

The SQL shown will return all comma separated items.  If you change the separator to be double-quote,comma,double-quote then the SQL will split the string only when the comma is not part of the string.

Then it's a simple matter of testing that each returned item starts and ends with a double-quote.  Technically, you only have to test the leading character of the first parameter and the last character of the last parameter, but it's easier SQL to test them all.

SELECT case when left (parameter, 1) = '"' and right (parameter, 1) = '"' then 'Parameter Passed' else '** parameter not a string **' end, parameter
FROM elements (SELECT somestring FROM sometable);



Good Luck,
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LuckyLucksAuthor Commented:
In listing 5 of the link,
SELECT VARCHAR(elem, 20)
FROM   TABLE ( elements('abc, def, ghi, 123') ) AS t(elem);  returns the values.

I dont undestand somethhing here- if the function elements is retuning a table, why is there a need to cast to TABLE. I would have thought SELECT * from elements ('elements('abc, def, ghi, 123')  would have sufficed.

Thanks
0
Kent OlsenDBACommented:
It's a parser thing.  IBM's is more rigid than some of the others and imposes some of its own rules.

FROM must be followed by a table specification.  My suspicion is that the parser doesn't attempt to evaluate the function call return type until further in the evaluation process and throws the error during the first pass when it doesn't see a table specifier.


Kent
0
LuckyLucksAuthor Commented:
SELECT *
FROM TABLE(elements('abc, def, ghi, 123') )
doesnt work either :(.

Could u advise how the above table can be fit into the SQL statement u have below.

SELECT case when left (parameter, 1) = '"' and right (parameter, 1) = '"' then 'Parameter Passed' else '** parameter not a string **' end, parameter
FROM elements (SELECT somestring FROM sometable);

0
Kent OlsenDBACommented:

I'm shooting from the hip here, so bear with me.  It would seem that:

SELECT case when left (parameter, 1) = '"' and right (parameter, 1) = '"' then 'Parameter Passed' else '** parameter not a string **' end, parameter
FROM TABLE (elements (SELECT somestring FROM sometable));

Should work.  Note that 'SELECT somestring FROM sometable' needs to return a single row.

Have you tried it passing a constant?

SELECT case when left (parameter, 1) = '"' and right (parameter, 1) = '"' then 'Parameter Passed' else '** parameter not a string **' end, parameter
FROM TABLE (elements ('"abc","def","ghi","jkl"'));


Kent
0
LuckyLucksAuthor Commented:
42601(-104)[IBM][CLI Driver][DB2/6000] SQL0104N  An unexpected token "FIRST" was found following "'',''1234''')) FETCH".  Expected tokens may include:  "FETCH".  SQLSTATE=42601
 (0.00 secs)

Besides, the parameter is not a prenamed column for the table returned by elements function, so should this be something else?

thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.