Solved

Checking for regular expressions in a string field

Posted on 2007-04-03
12
898 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
ID: 18846252
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
ID: 18846384
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
ID: 18846980
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 13

Expert Comment

by:ghp7000
ID: 18850552
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
ID: 18851109
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
ID: 18852613
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
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 18853057
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
ID: 18853246
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
ID: 18853439
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
ID: 18853531
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
ID: 18853600

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
ID: 18853806
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 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