Concat a text string gives space between?? 1

[scottyjohn]

Hi all,
I am running the following sql to convert a decimal string to hex and its fine but I then need to compare that value to a different data source which already has the value in hex.  Trouble is the second source appears to have two have two zeros at the start of each hex string, so I figure I need to manually add these to the start of the converted string.

SELECT 
EMPLOYEE_ID, PLACE, STATUS, STARTDATE, STARTT, ENDT, DURATION, CONCAT('00',TO_CHAR(CONNID,'XXXXXXXXXXXXXX')) 
FROM SOF.V_STATUS WHERE EMPLOYEE_ID ='Mouse_M_34317' 
AND STARTDATE='25/06/2009'

The issue I have is that when I do this, the result of the CONNID field is '00 7201B0CE7DA7EE' and the space at the start will cause me a problem.  If I reduce the X's in the TO_CHAR statement by one, the I get this result '00###############' which I believe is because I havent specified enough characters in the to_cahr statement.

Im not sure what I am doing wrong and how to get rid of that strange space which appears?

John

 

[SantaMufasa]

ScottyJohn,

You can get rid of the intervening blank(s) with the Oracle function LTRIM, which left trims whatever characters you want from a string...the default is blank spaces:

  1* select CONCAT('00',TO_CHAR(12345,'XXXXXXXXXXXXXX')) from dual
SQL> /

CONCAT('00',TO_CH
-----------------
00           3039
SQL> select CONCAT('00',ltrim(TO_CHAR(12345,'XXXXXXXXXXXXXX'))) from dual;

CONCAT('00',LTRIM
-----------------
003039

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

Incidentally, I believe the extra space you get when doing a TO_CHAR() on a number, is there to accommodate a minus sign if the number is negative. There probably is a way to suppress it in the format mask, but like Santa I generally just reach for an LTRIM() to get rid of it.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Beilstwh]

The clause you are looking for is FM


select CONCAT('00',TO_CHAR(12345,'XXXXXXXXXXXXXX')) from dual;

CONCAT('00',TO_CH
-----------------
00 3039

select CONCAT('00',TO_CHAR(12345,'FMXXXXXXXXXXXXXX')) from dual;


CONCAT('00',TO_CH
-----------------
003039

Bill
Lead Application Developer
New York State, USA