Home > mailing lists

Thread: opposite of chr(int)

opposite of chr(int)

From

Henry Drexler

Date:

06 October 2011, 19:42:39

Looking at the string functions on:

http://www.postgresql.org/docs/9.0/static/functions-string.html

I only see chr(int) and not something like int(chr).

reading through the others on that page i do not see a int(chr)

the goal is to tell whether or not a character in a string is an integer or not

my current workaround is this:

select

case

when substring('wallawa9kl',8,1) = '0' then 'true'

when substring('wallawa9kl',8,1) = '1' then 'true'

when substring('wallawa9kl',8,1) = '2' then 'true'

when substring('wallawa9kl',8,1) = '3' then 'true'

when substring('wallawa9kl',8,1) = '4' then 'true'

when substring('wallawa9kl',8,1) = '5' then 'true'

when substring('wallawa9kl',8,1) = '6' then 'true'

when substring('wallawa9kl',8,1) = '7' then 'true'

when substring('wallawa9kl',8,1) = '8' then 'true'

when substring('wallawa9kl',8,1) = '9' then 'true'

else 'false'

end

just wondering if there is something more compact or if this is an okay workaround solution.

Re: opposite of chr(int)

From

Gary Chambers

Date:

06 October 2011, 20:24:37

Henry,

> select
> case
> when substring('wallawa9kl',8,1) = '0' then 'true'
> when substring('wallawa9kl',8,1) = '1' then 'true'
> when substring('wallawa9kl',8,1) = '2' then 'true'
> when substring('wallawa9kl',8,1) = '3' then 'true'
> when substring('wallawa9kl',8,1) = '4' then 'true'
> when substring('wallawa9kl',8,1) = '5' then 'true'
> when substring('wallawa9kl',8,1) = '6' then 'true'
> when substring('wallawa9kl',8,1) = '7' then 'true'
> when substring('wallawa9kl',8,1) = '8' then 'true'
> when substring('wallawa9kl',8,1) = '9' then 'true'
> else 'false'
> end

> just wondering if there is something more compact or if this is an okay
> workaround solution.

How about:

CREATE OR REPLACE FUNCTION has_digit(text)
  RETURNS boolean
  LANGUAGE sql
AS $function$
select case when substring($1, '\d') is not null then true
else false
end
$function$

-- Gary Chambers

Re: opposite of chr(int)

From

Henry Drexler

Date:

06 October 2011, 21:18:29

On Thu, Oct 6, 2011 at 3:57 PM, Richard Broersma <richard.broersma@gmail.com> wrote:

On Thu, Oct 6, 2011 at 12:42 PM, Henry Drexler <alonup8tb@gmail.com> wrote:

How about: -- using 9.1

SELECT 'wallawa9kl' ~ '^.{7}\d';

or assuming that you always have 7 letters 1 digit followed by 2
letters having a fixed width of 10:

SELECT 'wallawa9kl' ~ '^[a-zA-Z]{7}\d[a-zA-Z]{2}$';

--
Regards,
Richard Broersma Jr.

I believe that would be a regular expression right? I will look that up in the manual, thank you for the succinct answer.

Re: opposite of chr(int)

From

Henry Drexler

Date:

06 October 2011, 21:19:24

Thank you, I will try that out tonight.

On Thu, Oct 6, 2011 at 4:15 PM, Gary Chambers <gwchamb@gwcmail.com> wrote:

Henry,

select
case
when substring('wallawa9kl',8,1) = '0' then 'true'
when substring('wallawa9kl',8,1) = '1' then 'true'
when substring('wallawa9kl',8,1) = '2' then 'true'
when substring('wallawa9kl',8,1) = '3' then 'true'
when substring('wallawa9kl',8,1) = '4' then 'true'
when substring('wallawa9kl',8,1) = '5' then 'true'
when substring('wallawa9kl',8,1) = '6' then 'true'
when substring('wallawa9kl',8,1) = '7' then 'true'
when substring('wallawa9kl',8,1) = '8' then 'true'
when substring('wallawa9kl',8,1) = '9' then 'true'
else 'false'
end

just wondering if there is something more compact or if this is an okay
workaround solution.

How about:

CREATE OR REPLACE FUNCTION has_digit(text)
RETURNS boolean
LANGUAGE sql
AS $function$
select case when substring($1, '\d') is not null then true
else false
end
$function$

-- Gary Chambers

Re: opposite of chr(int)

From

Merlin Moncure

Date:

06 October 2011, 21:23:36

On Thu, Oct 6, 2011 at 2:42 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
> Looking at the string functions on:
> http://www.postgresql.org/docs/9.0/static/functions-string.html
> I only see chr(int) and not something like int(chr).
> reading through the others on that page i do not see a int(chr)
> the goal is to tell whether or not a character in a string is an integer or
> not
> my current workaround is this:
> select
> case
> when substring('wallawa9kl',8,1) = '0' then 'true'
> when substring('wallawa9kl',8,1) = '1' then 'true'
> when substring('wallawa9kl',8,1) = '2' then 'true'
> when substring('wallawa9kl',8,1) = '3' then 'true'
> when substring('wallawa9kl',8,1) = '4' then 'true'
> when substring('wallawa9kl',8,1) = '5' then 'true'
> when substring('wallawa9kl',8,1) = '6' then 'true'
> when substring('wallawa9kl',8,1) = '7' then 'true'
> when substring('wallawa9kl',8,1) = '8' then 'true'
> when substring('wallawa9kl',8,1) = '9' then 'true'
> else 'false'
> end

I think the regex approach is better, but for posterity there is a
reverse of chr() function -- ascii();
postgres=# select ascii('C');
 ascii
-------
    67
(1 row)

merlin

Re: opposite of chr(int)

From

Henry Drexler

Date:

06 October 2011, 21:49:14

On Thu, Oct 6, 2011 at 3:57 PM, Richard Broersma <richard.broersma@gmail.com> wrote:

SELECT 'wallawa9kl' ~ '^.{7}\d';

--
Regards,
Richard Broersma Jr.

Richard, could you tell me if I have interpreted your above expression correctly.

Referencing http://www.postgresql.org/docs/9.1/static/functions-matching.html

I believe this is saying

~ matches

^. starts with seven preceding characters

\d represents a digit

So it is saying look past seven characters "^.{7}" and check that next character if it is an interger "\d"

Is that a correct narrative for what is going on?

Re: opposite of chr(int)

From

Richard Broersma

Date:

07 October 2011, 15:15:47

On Thu, Oct 6, 2011 at 2:49 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
> So it is saying look past seven characters "^.{7}" and check that next
> character if it is an interger "\d"
> Is that a correct narrative for what is going on?

Well almost.  I read it as:

Test to see if a pattern exists where

'^'  from the beginning of the text string
'.{7}' match exactly seven characters what ever they happen to be
'\d'  next match exact one numeric character
and then if found ignore the remainder of the string.

--
Regards,
Richard Broersma Jr.

Re: opposite of chr(int)

From

Henry Drexler

Date:

07 October 2011, 16:33:20

thanks for the clarification. I appreciate it.

On Fri, Oct 7, 2011 at 11:15 AM, Richard Broersma <richard.broersma@gmail.com> wrote:

On Thu, Oct 6, 2011 at 2:49 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
> So it is saying look past seven characters "^.{7}" and check that next
> character if it is an interger "\d"
> Is that a correct narrative for what is going on?

Well almost. I read it as:

Test to see if a pattern exists where

'^' from the beginning of the text string
'.{7}' match exactly seven characters what ever they happen to be
'\d' next match exact one numeric character
and then if found ignore the remainder of the string.

--
Regards,
Richard Broersma Jr.