4

I am trying to concatenate numbers as text, with a dash (-) between the numbers I am concatenating.

I have figured out how to concatenate numbers as text:

CAST(variable1 AS VARCAHR) || CAST(variable2 AS VARCHAR)

I would like a dash between these, i.e.

CAST(variable1 AS VARCAHR) || CAST(- AS VARCHAR) || CAST(variable2 AS VARCHAR)

but this gives me an invalid SQL error. I think it might have something to do with the dash not being in unicode or something, I am not sure. Thank you for your help.

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
Chad Crowe
  • 1,260
  • 1
  • 16
  • 21
  • And to whoever did the -1, I spent like 2 hours trying to figure this out. The answer just wasn't obvious to someone newer at sqlite. – Chad Crowe Jul 20 '15 at 21:10

2 Answers2

13

Just concat it with a literal string (also you don't need the casts):

variable1 || '-' || variable2
Colonel Thirty Two
  • 23,953
  • 8
  • 45
  • 85
  • 1
    This answer is wrong. If you don't have CAST then the numbers won't appear. – Alex B May 31 '18 at 22:20
  • 2
    @AlexB You don't need the cast if the variables are actually numbers. I suspect you actually have empty strings or `NULL`s. `SELECT 1.0 || '-' || 2.0;` works fine for me. – Colonel Thirty Two Jun 01 '18 at 22:05
8

This answer above that got 4 votes is wrong. Without CAST then the numeric value will not appear.

The correct answer is:

CAST(variable1 AS VARCHAR)|| '-' ||  variable2
Alex B
  • 1,092
  • 1
  • 14
  • 39