SQLTeam.com | Weblogs | Forums

Adding string to a decimal number


#1

Hi All,

Quick one I'm sure, I'm having difficulty with a line of my query below.
When executed, this query prints a column named 'BackupSizeGB', of which SQL correctly casts as a decimal - I am happy with the output however i'd like to add the letters 'gb' on to the end.

I've tried converting the decimal or concatenating it to a string but SSMS is not happy about doing so.
Does anyone have any tips for me?

SELECT db.name AS DatabaseName, db.state_desc AS CurrentState,
case when MAX(b.backup_finish_date) is NULL then 'No Backup Completed' else convert(varchar(100),
MAX(b.backup_finish_date)) end AS LastBackupFinishedDateTime,
f.physical_device_name AS FileLocation,
cast(round(sum(cast(bs.backup_size AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2)) AS BackupSizeGB,
db.recovery_model_desc AS RecoveryModel
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily f on f.media_set_id=b.media_set_id
JOIN msdb.dbo.backupset bs ON f.media_set_id = bs.media_set_id
WHERE db.database_id NOT IN (2) AND db.database_id NOT IN ('1', '2', '3', '4') AND f.physical_device_name IS NOT NULL --sysdbs are excluded
GROUP BY db.name,f.physical_device_name, db.recovery_model_desc, db.state_desc, bs.backup_size
ORDER BY 2 DESC

Many thanks
Matt


#2
cast(cast(round(sum(cast(bs.backup_size AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2)) as varchar(50)) + ' gb' AS BackupSizeGB,

#3

Didn't realise it was that simple, thank you!


#4

Hi yosiasz

I am from 8th dimension

Please let me know
Why you didn't use

varcharName + cast(intName as varchar) 

#5

Hi yosiasz

I got it

I am from planet
T SQL :wink::wink::grinning::grinning::sunglasses::sunglasses: