substring question [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

substring question [SOLVED]

Post by rhaynes »

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 »

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 »

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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

ah, i doubted to post something on trim or preg_replace.

anyway, php and postgresql have both a trim function ;)

http://www.postgresql.org/docs/current/ ... tring.html
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

Post by rhaynes »

timvw wrote:ah, i doubted to post something on trim or preg_replace.

anyway, php and postgresql have both a trim function ;)

http://www.postgresql.org/docs/current/ ... tring.html
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 »

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
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

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 »

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

removed the comma in the trim command ---- now a new error..

Post by rhaynes »

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
Post Reply