Thread: find column name that has under score (_)

find column name that has under score (_)

From

akp geek

Date:

17 February 2011, 17:55:55

Hi all -

I am trying to write a query to find all the column names in database that has a underscore in it (_) example souce_id. I know like will not work , if where column_name like '%_%' Can you please help?

Regards

Re: find column name that has under score (_)

From

Radosław Smogura

Date:

17 February 2011, 18:05:21

akp geek <akpgeek@gmail.com> Thursday 17 February 2011 19:55:46
> Hi all -
>
>           I am trying to write a query to find all the column names in
> database that has a underscore in it (_) example souce_id. I know like will
> not work , if where column_name like '%_%' Can you please help?
>
> Regards
Try SELECT * from pg_attribute where attname not like E'%\\_%' .... - i'm not
familiar with this.

Re: find column name that has under score (_)

From

akp geek

Date:

17 February 2011, 18:07:35

thanks . It worked

Regards

On Thu, Feb 17, 2011 at 2:05 PM, Radosław Smogura <rsmogura@softperience.eu> wrote:

akp geek <akpgeek@gmail.com> Thursday 17 February 2011 19:55:46
> Hi all -
>
> I am trying to write a query to find all the column names in
> database that has a underscore in it (_) example souce_id. I know like will
> not work , if where column_name like '%_%' Can you please help?
>
> Regards
Try SELECT * from pg_attribute where attname not like E'%\\_%' .... - i'm not
familiar with this.

Re: find column name that has under score (_)

From

"David Johnston"

Date:

17 February 2011, 18:08:29

Try just using the string function “position”. You’ll need to check the documentation or wait for others to determine which specific system views you will need to obtain the column name (if you do not already know that part).

position(substring in string) int

Non-Zero (or maybe >= 0) indicates the substring was found – though I would test with some known sample data just to make sure before running it on the column names.

Dave

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of akp geek
Sent: Thursday, February 17, 2011 1:56 PM
To: pgsql-general
Subject: [GENERAL] find column name that has under score (_)

Hi all -

Regards

Re: find column name that has under score (_)

From

David Kerr

Date:

17 February 2011, 18:09:50

On Thu, Feb 17, 2011 at 01:55:46PM -0500, akp geek wrote:
- Hi all -
-
-           I am trying to write a query to find all the column names in
- database that has a underscore in it (_) example souce_id. I know like will
- not work , if where column_name like '%_%' Can you please help?
-
- Regards

select table_schema,table_name, column_name  from information_schema.columns where column_name like '%\\_%';

seems to do the trick.

Dave

Re: find column name that has under score (_)

From

akp geek

Date:

17 February 2011, 18:10:01

that's right. Thanks again all for the help

On Thu, Feb 17, 2011 at 2:09 PM, David Kerr <dmk@mr-paradox.net> wrote:

On Thu, Feb 17, 2011 at 01:55:46PM -0500, akp geek wrote:
- Hi all -
-
- I am trying to write a query to find all the column names in
- database that has a underscore in it (_) example souce_id. I know like will
- not work , if where column_name like '%_%' Can you please help?
-
- Regards

select table_schema,table_name, column_name from information_schema.columns where column_name like '%\\_%';

seems to do the trick.

Dave

Re: find column name that has under score (_)

From

hubert depesz lubaczewski

Date:

17 February 2011, 19:36:22

On Thu, Feb 17, 2011 at 01:55:46PM -0500, akp geek wrote:
> Hi all -
>
>           I am trying to write a query to find all the column names in
> database that has a underscore in it (_) example souce_id. I know like will
> not work , if where column_name like '%_%' Can you please help?

where column_name ~ '_'

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007