convert character varying to int

#41

In article <-I-dndcYsJ8ZLpbVnZ2dnUVZ_judnZ2d (AT) earthlink (DOT) com>, RJ <ruili_gc (AT) earthlink (DOT) net> wrote:

Quote:

EricF 写道:
In article <2cipd5-ttt.ln1 (AT) news (DOT) a-kretschmer.de>, Andreas Kretschmer
akretschmer (AT) spamfence (DOT) net> wrote:
begin RJ wrote:
Hi,
I'm using postgres sql 8.1x
anyone can help convert a character varying to int?
I have a table with :
attribute | character varying(20)
and I need to convert it to numbers so I can do some calculation.
tried
select cast(privilege.value as int)
simple example:

test=*# select regexp_replace('abc123def','[^0-9]','','g')::int * 10;
?column?
----------
1230

end
Andreas

It's not too clear what you want. If attribute only contains numbers it's
easier than Andreas' example:

select (CAST(attribute as int) *2) from ...

Eric
Hi,

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on

8.3 , only the table was missing from the code snippet.

Eric

#42

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#43

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#44

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#45

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#46

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#47

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#48

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#49

begin EricF schrieb:

Quote:

Thanks, I just get regexp_replace working!!!!
your guys are best!

Glad you got the regex working. I'm surprised the cast didn't. I tried it on
8.3 , only the table was missing from the code snippet.

a simple CAST can't work in this case, because the column contains text
(chars), not only digits.

,----[ quote ]

Quote:

it shows: ERROR: invalid input syntax for integer: "DENY"
`----

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net