Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
rhaynes
Forum Newbie
Posts: 18 Joined: Mon Nov 08, 2004 8:32 am
Post
by rhaynes » Sun Nov 28, 2004 5:25 pm
Hi, I have been using substring to strip the integer part of a alphanumeric for example for data like
3B or 193A
substring(var from '[0-9]*')::int
gets me the integer part...
How would I get the integer part of
A4
or
B55 ??
Is there anyway to write one substring command which will get me the integer part of the data if there are characters at the beginning or end??
R Haynes
I am now found a solution... I use btrim but it doesn't seem to accept a regular expression like [A-Z]* so instead I used
Code: Select all
select *, btrim(card_number,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')::int as cn .....
Thanks for everyone's help!
R Haynes
Last edited by
rhaynes on Mon Nov 29, 2004 1:04 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Sun Nov 28, 2004 6:43 pm
this would replace all characters that are not a digit by ""
Code: Select all
$integer = preg_replace("/\D/", "", $string);
rhaynes
Forum Newbie
Posts: 18 Joined: Mon Nov 08, 2004 8:32 am
Post
by rhaynes » Sun Nov 28, 2004 6:56 pm
timvw wrote: this would replace all characters that are not a digit by ""
Code: Select all
$integer = preg_replace("/\D/", "", $string);
How would this work in a query to postgresql ??
so I have
Code: Select all
$query = "select * from database order by ??? "
preg_replace is a php command right??
Thanks,
R Haynes
rhaynes
Forum Newbie
Posts: 18 Joined: Mon Nov 08, 2004 8:32 am
Post
by rhaynes » Sun Nov 28, 2004 7:53 pm
Hi, thanks again for your reply... I did try the following with trim over the last day or so...
Code: Select all
order by trim(both,'їA-Z]*' from card_number)::int
on the value '3G' (for example) it complains:
pg_exec(): Query failed: ERROR: invalid input syntax for integer: "3G"
Thanks,
R Haynes
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Mon Nov 29, 2004 4:20 am
rhaynes wrote: Code: Select all
order by trim(both,'їA-Z]*' from card_number)::int
you could try like this:
Code: Select all
SELECT *, trim(both,'їA-Z]*' from card_number)::int AS cn FROM cards ORDER BY cn
Maugrim_The_Reaper
DevNet Master
Posts: 2704 Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland
Post
by Maugrim_The_Reaper » Mon Nov 29, 2004 11:09 am
That would fail also, yes? If you've defined something as integer, passing anything not an integer will produce an error - 3G is not an integer...it's a string.
rhaynes
Forum Newbie
Posts: 18 Joined: Mon Nov 08, 2004 8:32 am
Post
by rhaynes » Mon Nov 29, 2004 11:18 am
Maugrim_The_Reaper wrote: That would fail also, yes? If you've defined something as integer, passing anything not an integer will produce an error - 3G is not an integer...it's a string.
The variable card_number is a character string which has a integer part either something like
3G
or
H4
or
HT4
or 3GT
or just an inter 27 or 28.
I did try this trim command but it seems to complain about a comma?? The error is:
Code: Select all
pg_exec(): Query failed: ERROR: syntax error at or near "," at character 20
Character 20 occurs around about:
select *, trim
-------
Any suggestions?
R Haynes
rhaynes
Forum Newbie
Posts: 18 Joined: Mon Nov 08, 2004 8:32 am
Post
by rhaynes » Mon Nov 29, 2004 11:21 am
Hi, I changed the command to
Code: Select all
select *, trim(both 'їA-Z]*' from card_number)
ie removing the comma after both... I now receive the error:
pg_exec(): Query failed: ERROR: invalid input syntax for integer: "3G"
which would seem to indicate that trim is not doing the job??
R Haynes