Find Columns with a Prefix
I am trying to identify all the columns that have been prefixed with '_'. This
is the simplified version of my SQL.
select tabname, colname from systables t, syscolumns c
where colname like '^_%'
and t.tabid = c.tabid
and tabname not like '%sys%'
--and tabname = 'cef_security'
I am able to find the columns using substr:
select tabname, colname from systables t, syscolumns c
where substr(colname,1,1) = '_'
and t.tabid = c.tabid
and tabname not like '%sys%'
and tabname = 'cef_security'
I have tried using LIKE and MATCHES with the same 'no rows' results. Asking
what the appropriate usage is for this purposes.
get rid of the carrot:
where colname like _%
cheers
j.
> On Mar 6, 2018, at 12:45 PM, MURALI PAZHAYANNUR <pmurali@ftportfolios.com>
wrote:
>
> I am trying to identify all the columns that have been prefixed with '_'.
This
> is the simplified version of my SQL.
>
> select tabname, colname from systables t, syscolumns c
> where colname like '^_%'
> and t.tabid = c.tabid
> and tabname not like '%sys%'
> --and tabname = 'cef_security'>
> I am able to find the columns using substr:
>
> select tabname, colname from systables t, syscolumns c
> where substr(colname,1,1) = '_'
> and t.tabid = c.tabid
> and tabname not like '%sys%'
> and tabname = 'cef_security'>
> I have tried using LIKE and MATCHES with the same 'no rows' results. Asking
> what the appropriate usage is for this purposes.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Colname[1,1] = "_"
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MURALI
PAZHAYANNUR
Sent: Tuesday, March 06, 2018 11:46 AM
To: ids@iiug.org
Subject: Find Columns with a Prefix [40793]
I am trying to identify all the columns that have been prefixed with '_'.
This
is the simplified version of my SQL.
select tabname, colname from systables t, syscolumns c
where colname like '^_%'
and t.tabid = c.tabid
and tabname not like '%sys%'
--and tabname = 'cef_security'
I am able to find the columns using substr:
select tabname, colname from systables t, syscolumns c
where substr(colname,1,1) = '_'
and t.tabid = c.tabid
and tabname not like '%sys%'
and tabname = 'cef_security'
I have tried using LIKE and MATCHES with the same 'no rows' results. Asking
what the appropriate usage is for this purposes.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Try:
select tabname, colname from systables t, syscolumns c where colname matches'_*'
and t.tabid = c.tabid
and tabname not like '%sys%'
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MURALI
PAZHAYANNUR
Sent: Tuesday, March 06, 2018 10:46 AM
To: ids@iiug.org
Subject: Find Columns with a Prefix [40793]
I am trying to identify all the columns that have been prefixed with '_'.
This is the simplified version of my SQL.
select tabname, colname from systables t, syscolumns c where colname like'^_%'
and t.tabid = c.tabid
and tabname not like '%sys%'
--and tabname = 'cef_security'
I am able to find the columns using substr:
select tabname, colname from systables t, syscolumns c wheresubstr(colname,1,1) = '_'
and t.tabid = c.tabid
and tabname not like '%sys%'
and tabname = 'cef_security'
I have tried using LIKE and MATCHES with the same 'no rows' results. Asking
what the appropriate usage is for this purposes.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you for your responses. I had messed up the 'escape character' in my
initial attempts with LIKE. Here is the correct syntax using LIKE.
select tabname, colname
fromsystables t, syscolumns c
--where colname matches '_*'
where colname like '\\\\_'
and t.tabid = c.tabid
and tabname not like '%sys%'