CODEWITHSUNDEEP
sqlsqlite
margercastigador
margercastigador

Reputation: 9

Rearranging HEX string in SQL

I have this Blob person_profile ID that looks like this.

5FAABE4197CBA344A6C0735C33866BF6

I got that output using this query

SELECT HEX(person_profile_id) FROM person_profile

Now I need a query that rearranges it so that it will look like this

5FAABE41-97CB-A344-A6C0-735C33866BF6

Is this possible?

Upvotes: 0

Views: 96

Answers (1)

cdehaan
cdehaan

Reputation: 564

SUBSTRING lets your pull out parts of your hex string, while CONCAT allows you to put it back together, with some dashes thrown in.

SELECT CONCAT(
SUBSTRING(HEX(person_profile_id),1,8), '-', SUBSTRING(HEX(person_profile_id),9,4), '-', 
SUBSTRING(HEX(person_profile_id),13,4), '-', 
SUBSTRING(HEX(person_profile_id),17,4), '-', 
SUBSTRING(HEX(person_profile_id),21)
) AS id_with_dashes;

Upvotes: 1

Related Questions