SQL prefix a piece of data with a tag

Posted on 2012-09-10
Last Modified: 2012-09-10
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.
Question by:Smartin

    Author Comment

    Sorry just read my post - my spell check is broken, I blame it on the gremlins!!!!
    LVL 12

    Accepted Solution

    Try this

    'ref' + convert(varchar,dbo.Lead.LeadReference) AS [order ID]

    Author Comment

    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"

    Author Comment

    Ignore - being stupid, worked perfect!!!!


    Author Closing Comment

    Spot on
    LVL 65

    Expert Comment

    by:Jim Horn
    '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.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now