0

I have a height field that contains two numeric values and I need to add ' and " at the end of each number to display the value as height(inch) format. It seemed simple but couldn't find answers that I was looking for.

Height -> Height(inch)

72 -> 7'2"

65 -> 6'5"

68 -> 6'8" . . . . . . . .

Any suggestions? I am very new to sql and and would appreciate any help I can get!!

  • 1
    If the value I give you is 111, how can you tell if the value is 1'11" or 11'1"? – Cowthulhu May 3 '19 at 20:07
  • Good questions! But the height data set I am working with is consist of only two numeric value! – Claire May 3 '19 at 20:10
  • 1
    As long as both values are between 0-9, you could do CAST(SUBSTRING([value],0,1) AS char(1)) + '''' + CAST(SUBSTRING([value],1,1) AS char(1)) + '"' – Cowthulhu May 3 '19 at 20:13
  • 1
    Thanks so much! I had to tweak a little but it's exactly what I needed. Have a wonderful Friday! – Claire May 3 '19 at 20:29
  • 2
    I would expect 72 -> 6'0". – Rick James May 3 '19 at 23:43
1
mysql> select concat(LEFT(73, 1),'\'',SUBSTR(73, 2),'\"') converted;
+-----------+
| converted |
+-----------+
| 7'3"      |
+-----------+

Not the answer you're looking for? Browse other questions tagged or ask your own question.