spark.sql("select case when length(medicare)>0 then lpad(med,13,'0') else '' end as med from input").show(false)

In the above query I'm able to trim the characters to a specified length but what is the procedure to get the characters to a specified length with spaces before that.

sample input: 1234(10 spaces plus 1234) sample output: 1234(9 spaces plus 1234)

Whatever the input is it should trim to 13 characters.

sample input: 1234567890123(10 spaces plus 1234567890123) sample output:1234567890123(13 characters)

Please help me out on this...Thanks in advance

CodePudding user response：

You can take the right-most 13 spaces and pad that to 13:

select lpad(right(medicare, 13), 13, ' ')

The right() function is just to handle the case where medicare might have more than 13 characters.

EDIT:

Based on the comment:

select lpad(left(ltrim(medicare), 13), 13, ' ')