substr or other ad hoc structure to select everything before a space OR a comma

Jim Aloye

substr or other ad hoc structure to select everything before a space OR a comma

I have a DB2 Ad Hoc query that looks like this

 

SELECT    X            

                ,Y            

                ,Z

                ,SPECIAL

 

FROM MYTABLE

 

 

The column SPECIAL contains string data

 

Somewhere in the string there is either a BLANK SPACE or a COMMA not both

 

I need to get everything on the left, that comes before the space or comma

If I use the query shown below, I get a bunch of nulls in rows where there is a comma instead of a space

 

SELECT    X            

                ,Y            

                ,Z

, SUBSTR(SPECIAL, 1,(PosStr(SPECIAL, ' ') -1)) AS MY FIELD,

 

FROM MYTABLE

 

 

If I use the query shown below, I get a bunch of nulls in rows where there is a space instead of a comma

 

SELECT    X            

                ,Y            

                ,Z

, SUBSTR(SPECIAL, 1,(PosStr(SPECIAL, ',') -1)) AS MY FIELD,

 

FROM MYTABLE

 

 

I do not have access to stored procedures or any administrative functionality. It must take the form of an ad hoc query.

 

If anyone can provide some insight on what I must do to meet my objective, I would greatly appreciate it.

Jim Aloye

RE: substr or other ad hoc structure to select everything before a space OR a comma
(in response to Jim Aloye)

attn admin: Please delete this post I put it in the wrong section. I don't seem to have the right to delete my own post.