Solved

Checking for regular expressions in a string field

Posted on 2007-04-03
12
897 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

22 Experts available now in Live!

Get 1:1 Help Now