SQL prefix a piece of data with a tag

Please can you help - I am struggling with such a basic thing and my brain doesnt want to work.  I need to prefix a piece of data from one of my quiries:

I need the "orderID" to have the words ref bfore the data:
   001 would be ref001
   002 would be ref002 and so on...

SELECT     dbo.Opportunity.ContactEmail AS [email address], dbo.Opportunity.ContactFullName AS [customer name], dbo.Lead.LeadReference AS [order ID]
FROM         dbo.Lead INNER JOIN
                      dbo.Opportunity ON dbo.Lead.RecId = dbo.Opportunity.ParentLink_RecID
WHERE     (NOT (dbo.Opportunity.ContactEmail IS NULL))

Everything I try doesnt work I am struggling to use the correct words on  google.
Who is Participating?
Jared_SConnect With a Mentor Commented:
Try this

'ref' + convert(varchar,dbo.Lead.LeadReference) AS [order ID]
SmartinAuthor Commented:
Sorry just read my post - my spell check is broken, I blame it on the gremlins!!!!
SmartinAuthor Commented:
I already tried that (sorry should havbe said) - I get a "SQL execution error"

"... conversaion failed when converting the varchar value 'ref' to a data type"
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

SmartinAuthor Commented:
Ignore - being stupid, worked perfect!!!!

SmartinAuthor Commented:
Spot on
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
'ref' + CAST(orderID as varchar(10)) as your_new_order_number

When you concatenate a character with an int you have to cast the int to a character value.
All Courses

From novice to tech pro — start learning today.