By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,821 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,821 IT Pros & Developers. It's quick & easy.

String manipulation

P: n/a
Hello to all,

I have small problem. I have some database fields (VARCHAR)
and these field should hold some ahex coded values. So the
string length must be even and each character can only be
0-9, a-f or A-F.
My idea was that:

====8<-----------------------------------

CREATE TABLE test (
id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
text VARCHAR(150) NOT NULL CHECK(isAHex(text))
);

CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
text_p ALIAS FOR $1;
BEGIN
IF ((length(text_p) % 2) <> 0) THEN
RETURN FALSE;
END IF;
-- TODO How to check each character
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

====8<-----------------------------------

Has anybody an idea how could I check each character?
I would prefer a solution in plpgsql!!

Thank!
pativo

--
\\://
(- 0)
---ooO-(_)-Ooo---
Nov 22 '05 #1
Share this Question
Share on Google+
9 Replies

P: n/a
pativo wrote:
Hello to all,

I have small problem. I have some database fields (VARCHAR)
and these field should hold some ahex coded values. So the
string length must be even and each character can only be
0-9, a-f or A-F.
My idea was that:

====8<-----------------------------------

CREATE TABLE test (
id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
text VARCHAR(150) NOT NULL CHECK(isAHex(text))
);

CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
text_p ALIAS FOR $1;
BEGIN
IF ((length(text_p) % 2) <> 0) THEN
RETURN FALSE;
END IF;
-- TODO How to check each character
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

====8<-----------------------------------

Has anybody an idea how could I check each character?
I would prefer a solution in plpgsql!!

Thank!
pativo

Here's one solution:
CREATE OR REPLACE FUNCTION isAHex(text) RETURNS boolean as '

DECLARE
inputText ALIAS FOR $1;
tempChar text;
isHex boolean;
BEGIN
isHex = true;
IF ((length(inputText) % 2) <> 0) THEN
return FALSE;
END IF;
FOR i IN 1..length(inputText) LOOP
tempChar := substr(inputText, i, 1);
IF tempChar ~ ''[g-z]'' THEN
return FALSE;
ELSE IF tempChar ~ ''[G-Z]'' THEN
return FALSE;
END IF;
END LOOP;

return isHex;
END;
' LANGUAGE 'plpgsql';

You may have to check the IF...ELSE IF... stuff but this should work.
I've used a very similar one to check if a value is numeric.

Ron

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #2

P: n/a
On Tue, Feb 17, 2004 at 03:46:53 -0800,
pativo <pa****@arcor.de> wrote:
Hello to all,

I have small problem. I have some database fields (VARCHAR)
and these field should hold some ahex coded values. So the
string length must be even and each character can only be
0-9, a-f or A-F.


You can use a constraint that checks the value versus a regular
expression. Something like: ~ '^([0-9a-fA-F][0-9a-fA-F])+$^'
Unless the 150 character limit is a real business rule, you probably
want to use TEXT instead of VARCHAR(150).

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #3

P: n/a
You should be able to do that with a regular expression.

CHECK (text ~ '^([0-9A-F]{2})+$')

Remark: As the column is NOT NULL, I suppose that an empty string is not
valid. If an empty string must be valid, replace the + with * ;-)
pativo wrote:
Hello to all,

I have small problem. I have some database fields (VARCHAR)
and these field should hold some ahex coded values. So the
string length must be even and each character can only be
0-9, a-f or A-F.
My idea was that:

====8<-----------------------------------

CREATE TABLE test (
id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
text VARCHAR(150) NOT NULL CHECK(isAHex(text))
);

CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
text_p ALIAS FOR $1;
BEGIN
IF ((length(text_p) % 2) <> 0) THEN
RETURN FALSE;
END IF;
-- TODO How to check each character
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

====8<-----------------------------------

Has anybody an idea how could I check each character?
I would prefer a solution in plpgsql!!

Thank!
pativo


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #4

P: n/a
Pascal Polleunus wrote:
You should be able to do that with a regular expression.

CHECK (text ~ '^([0-9A-F]{2})+$')

Remark: As the column is NOT NULL, I suppose that an empty string is not
valid. If an empty string must be valid, replace the + with * ;-)


I just noticed an unusual affect. GUC regex_flavor affects CHECK
constraints even after the check constraint has been created:

test=> SET regex_flavor = 'advanced'; -- default
SET
?column?
----------
t
(1 row)

test=> SET regex_flavor = 'basic';
SET
test=> SELECT 'abc' ~ '(a|x).*';
?column?
----------
f
(1 row)

test=> SET regex_flavor = 'advanced';
SET
test=> CREATE TABLE test (x TEXT CHECK (x ~ '(a|x).*'));
CREATE TABLE
test=> INSERT INTO test VALUES ('a');
INSERT 17149 1
test=> SET regex_flavor = 'basic';
SET
test=> INSERT INTO test VALUES ('a');
ERROR: new row for relation "test" violates check constraint "test_x"

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #5

P: n/a
Bruce Momjian <pg***@candle.pha.pa.us> writes:
I just noticed an unusual affect. GUC regex_flavor affects CHECK
constraints even after the check constraint has been created:


Why does that surprise you?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #6

P: n/a
\Tom Lane wrote:
Bruce Momjian <pg***@candle.pha.pa.us> writes:
I just noticed an unusual affect. GUC regex_flavor affects CHECK
constraints even after the check constraint has been created:


Why does that surprise you?


I don't think it is good practice for a CHECK constraint to change its
behavior based on a GUC variable. I am not surprised, but am pointing
out it isn't ideal.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #7

P: n/a
Bruce Momjian <pg***@candle.pha.pa.us> writes:
I don't think it is good practice for a CHECK constraint to change its
behavior based on a GUC variable.


You can develop comparable "failure scenarios" for any of the GUC
variables that affect query semantics --- timezone, sql_inheritance,
you name it. Locking them all down when a check constraint or function
or view is created seems impractical ... and if we did do it then we'd
get complaints about that too. ("What do you mean I can't change the
setting later?")

In practice I think we have to assume that those variables are set
consistently within any one application. If you go frobbing them
on-the-fly then you're going to have issues.

I suppose paranoid sorts might lobby to make any GUC variable that can
change query semantics be a superuser-only setting, but to me that cure
sounds worse than the disease.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #8

P: n/a
Tom Lane wrote:
Bruce Momjian <pg***@candle.pha.pa.us> writes:
I don't think it is good practice for a CHECK constraint to change its
behavior based on a GUC variable.


You can develop comparable "failure scenarios" for any of the GUC
variables that affect query semantics --- timezone, sql_inheritance,
you name it. Locking them all down when a check constraint or function
or view is created seems impractical ... and if we did do it then we'd
get complaints about that too. ("What do you mean I can't change the
setting later?")

In practice I think we have to assume that those variables are set
consistently within any one application. If you go frobbing them
on-the-fly then you're going to have issues.

I suppose paranoid sorts might lobby to make any GUC variable that can
change query semantics be a superuser-only setting, but to me that cure
sounds worse than the disease.


What concerned me is that it would actually make data the passed the
CHECK constraint initially fail later. Look at this:

test=> CREATE TABLE test (x TEXT CHECK (x ~ '(a|x).*'), y INT);
CREATE TABLE
test=> INSERT INTO test VALUES ('a', 1);
INSERT 380556 1
test=> SET regex_flavor = 'basic';
SET
test=> UPDATE test SET y=2;
ERROR: new row for relation "test" violates check constraint "test_x"

The UPDATE fails even when the row isn't changed. Certainly interesting.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #9

P: n/a
pa****@arcor.de (pativo) wrote in message news:<58**************************@posting.google. com>...

Hello and thanks to all!

I'm amused that I get so many responses.

Ok, at time I use a plpgsql function (isAHex(VARCHAR)) which checks only
if the length is even.
So I think I should implement the following.

=======8<-------------------------------------------------------------------

CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
input_text_p ALIAS FOR $1;
tmp_char VARCHAR;
BEGIN
IF ((length(input_text_p) % 2) <> 0) THEN
RETURN FALSE;
END IF;
FOR i IN 1..length(input_text_p) LOOP
tmp_char := substr(input_text_p, i, 1);
IF NOT tmp_char ~ ''[0-9a-fA-F]'' THEN
RETURN FALSE;
END IF;
END LOOP;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE textsTest (
surrogate VARCHAR(40) PRIMARY KEY,
hex_text VARCHAR(150) NOT NULL CHECK(isAHexTest(hex_text))
);

=======8<-------------------------------------------------------------------
But for next release I will use the following. This is in my eyes
the better solution.

=======8<-------------------------------------------------------------------

CREATE TABLE textsTest (
surrogate VARCHAR(40) PRIMARY KEY,
hex_text TEXT NOT NULL CHECK(text ~ '^([0-9A-Fa-f]{2})+$')
);

=======8<-------------------------------------------------------------------
Thanks to all!!!

oki,
pativo

--
www.pativo.de
Nov 22 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.