text format

[yelin_nyu]

Hi,

I did something to ID, and it's returning as number instead of string. My tbls won't get to join properly because things didn't match.

tblX:

sql select

left(right(ID, 5), 4)+ '0' as NID

from xxx.qvd;

left join (tblX)

sql select left(PIN, 9) +'0' as NID

FROM tbl123;

i want to make sure NID is a 5 digit code with ending equal to 0.

ie. '00050' should not be 5

[MayilVahanan]

HI

Use

text() function

Hope it helps

[yelin_nyu]

hi,

i tried

tblX:

sql select

text(left(right(ID, 5), 4)+ '0') as NID

from xxx.qvd;

left join (tblX)

sql select text(left(PIN, 5) +'0') as NID

FROM tbl123;

but didn't work

'00050' is returning as 5, not values pulled from tbl123 because tblX.NID and tbl123.NID do not match

[MayilVahanan]

HI

Try like this

text(left(right(ID, 5), 4))& '0' as NID

Hope it helps

[Anonymous]

use & instead

text(left(right(ID, 5), 4)+ '0') as NID => left(right(ID, 5), 4) & '0' as NID

[CELAMBARASAN]

As per your logic 5 digit will be equal to 9 digit? how it is possible?

Then you should use & instead of + here.

I may misunderstood can you upload a sample file?

[yelin_nyu]

typo.... it's 5

tblX:

sql select

left(right(ID, 5), 4)+ '0' as NID

from xxx.qvd;

left join (tblX)

sql select left(PIN, 5) +'0' as NID

FROM tbl123;

[MayilVahanan · Accepted solution]

Hi

PFA