Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Cast varchar to integer |
Thu, Jun 13 2019 6:54 AM | Permanent Link |
Heiko Knuettel | 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 |
Thu, Jun 13 2019 9:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Heiko
How about using POS eg select POS(SUBSTR(_Surname,1,1),'ABCEDFGHIJKLMNOPQRSTUVWXYZ') from members Roy Lambert |
Thu, Jun 13 2019 9:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Jun 13 2019 10:14 AM | Permanent Link |
Heiko Knuettel | 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 |
Thu, Jun 13 2019 11:43 AM | Permanent Link |
Heiko Knuettel | >>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! |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |