CODEWITHSUNDEEP
ibm-mqmessagebrokeribm-integration-busextended-sql
CaptainFreedom
CaptainFreedom

Reputation: 127

How to convert hexBinary (MsgId) to character in esql?

I'm trying to put MQMD.MsgId to XMLNSC.MsgId field, like this:

SET OutputRoot.XMLNSC.Root.MsgId = InputRoot.MQMD.MsgId;

But I'm getting X'414d51204d39392e5352442e4330302e56c47bd4203b3708' instead of just 414d51204d39392e5352442e4330302e56c47bd4203b3708.

Also i've tried to cast MsgId to CHARACTER, but result is the same.

How to get rid of quotes and 'X'?

Upvotes: 0

Views: 3420

Answers (3)

TJA
TJA

Reputation: 3041

With ESQL SUBSTRING you can also use BEFORE and AFTER

DECLARE MsgIdAsChar CHAR SUBSTRING(SUBSTRING(CAST(InputRoot.MQMD.MsgId AS CHAR) AFTER '''') BEFORE '''');

BEFORE and AFTER are also extremely handy when it comes to looking for things in strings.

DECLARE TheHaystack CHAR ‘Where a value is 99 out of a maximum value of 100);
DECLARE TheValue CHAR SUBSTRING(SUBSTRING(TheHaystack AFTER 'value is ') BEFORE ' ');

Upvotes: 1

itfromblighty
itfromblighty

Reputation: 21

I don't have enough reputation to 'add a commment' yet, but working with so many languages, I've learned to dislike SUBSTRING and LENGTH type functions. In the case of answer 'Feb 28 2016 at 10:41' above (yes I found this answer in response to my own search - for the "CAST(InputRoot.MQMD.MsgId AS CHAR)" above, I chose to use this instead:

DECLARE MsgId CHAR RIGHT(LEFT(CAST(InputRoot.MQMD.MsgId AS CHAR),50),48);

This assumes MsgId is always 24 hex pairs long (24x2=48) and goes for LEFT 50 first (taking off the last single quote), then RIGHT 48 (taking off the first X char and the first single quote). Does anyone disagree with the assumption? It also has the bonus of condensing to one line as it's not necessary to have two references to "MsgId" in 'SUBSTRING'

Upvotes: 2

Attila Repasi
Attila Repasi

Reputation: 1830

You could try something like this:

DECLARE msgId CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER);
SET OutputRoot.XMLNSC.Root.MsgId = SUBSTRING(msgId FROM 3 FOR LENGTH(msgId) - 3);

Upvotes: 1

Related Questions