Solved

Checking for regular expressions in a string field

Posted on 2007-04-03
12
895 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:LuckyLucks
12 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
 

Author Comment

by:LuckyLucks
Comment Utility
and what if there are more elements than the three names like unknown number of names at the time of writing the SQL?
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 

Author Comment

by:LuckyLucks
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
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
 

Author Comment

by:LuckyLucks
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 

Author Comment

by:LuckyLucks
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

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
 

Author Comment

by:LuckyLucks
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now