Hi there,

I was looking for a way to convert a char to an integer in SQL, for the purpose of an alphabetical sort of a varchar. Thought about using the first 3 characters, converting them to their ascii code, multiplying the first two with 1.000.000 and 1.000, adding them together, which would be sufficient for a alphabetical order.

(yes, I know that I can index a varchar field, but I need a sort order that starts out alphabetical but can be sorted manually afterwards)

Searched the manual for an equivalent of the chr() function in Delphi, wasn't surprised to find none, but was surprised that according to the manual you can actually cast a varchar to an integer.

Happily tried that - and got a conversion error. Varchars not working, single chars not working. That's a pity. I get it that varchars can't be casted to Integers, but in some future version being able to cast a single char to an integer  would be nice.

Cheers,

Heiko

Heiko


How about using POS eg

select POS(SUBSTR(_Surname,1,1),'ABCEDFGHIJKLMNOPQRSTUVWXYZ') from members

Roy Lambert

Heiko


Couple more thoughts. You can of course encapsulate the POS approach in a function

CREATE FUNCTION "ORD" (IN "Inwards" VARCHAR COLLATE "ANSI_CI")
RETURNS INTEGER
BEGIN
RETURN POS(UPPER(Inwards),'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
END

and could extend this to however many characters you want to use. You could also (wrongly in my opinion) make it case sensitive.

More importantly - why? What about just adding a couple of extra characters after the three you are going to use, set them to '0' and you have up to 99 subsorts within each alpha sort. You can adjust the overall sorting by altering the alpha portion.

Roy Lambert

Roy

That's some pretty nice SQL magic...thank you!!

btw, "varchar to integer results in conversion error": when you get caught up in something so hard that you totally forget the fact that you have been converting the string '100' to the integer 100 for ages...facepalm

>>What about just adding a couple of extra characters after the three you are going to use, set them to '0' and you have up to 99 subsorts within each alpha sort. You can adjust the overall sorting by altering the alpha portion.<<

After some tests, I think I will go that way. Again, thank you very much!