CODEWITHSUNDEEP
sqlsql-server
Becca
Becca

Reputation: 189

Adding a dash in a sql query

 SELECT SUBSTRING(day1,1)+'-'+SUBSTRING(Sum(day1 + day2)) AS day2
    FROM   DaysTable
    WHERE day1 is Not null AND  day2 is Not null

I'm trying to return values from two columns in the same table and also add the two columns which and also add the two columns which will return as one acolumn. Also i want to add a dash line between the results.So my results will look like this 20-30

Upvotes: 2

Views: 12824

Answers (3)

Hart CO
Hart CO

Reputation: 34774

If the fields are integers:

SELECT CAST(day1 AS VARCHAR(25))+'-'+CAST(day1+day2 AS VARCHAR(25))
FROM   DaysTable
WHERE day1 is Not null AND  day2 is Not null

If they are strings you could omit the first CAST() and would have to add a CAST() around day1 to prompt integer addition:

SELECT day1+'-'+CAST(CAST(day1 AS INT)+day2 AS VARCHAR(25))
FROM   DaysTable
WHERE day1 is Not null AND  day2 is Not null

Numbers shouldn't be stored as strings except in instances where no calculations would be done on them and/or you want to preserve leading zeroes, like SSN/phone numbers/CPT codes.

Fyi: SUBSTRING() returns a portion of a string (a 'sub'set if you will):

SELECT SUBSTRING('dog',1,2) 
-- Returns 'do', from the first character, take 2 characters.

SUM() is not needed for adding values on the same row, but is instead used when you want to aggregate values from multiple rows. For adding or concatenating, all you need is + and whether it concatenates or adds is determined by the data types presented.

Upvotes: 2

Lewis Worley
Lewis Worley

Reputation: 279

I run into this all the time when I try to concatenate strings and integers, kind of same problem.

SUBSTRING is expecting a string of some sort, and isn't happy that you're giving it an integer.

So, convert your integers to strings before you do string operations on them.

For example:

SELECT SUBSTRING(CAST(day1 as varchar(2)),1) + '-'

Upvotes: 0

taotechnocom
taotechnocom

Reputation: 228

try this

SELECT SUBSTRING(CAST(day1 AS NVARCHAR(50)),1,2)+'-'+SUBSTRING(CAST(Sum(day1 + day2)AS NVARCHAR(50)),1,2) AS day2

Upvotes: 0

Related Questions