Unable to append strings to record from SELECT Statement

  • Hi All,

     

    Just had another newbie question, I practiced some sql commands on the w3 schools sandbox, basically my goal is to take all records from a column and append a string before each record. So in the W3 SQL Sandbox, https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

    I can type in the query

    SELECT 'A1-' || CustomerID
    FROM Customers

    And for each record, before Customer ID, it appends the Text A1- e.g. A1-1, A1-2.....

    However, when I try to run the same query (with relevant field and table names) on SQL 2018 Management Studio, it gives me an error. Can I run this query on SQL 2018 Management Studio?

    • This topic was modified 4 months ago by  Imrans23.
  • SQL Server does not use double pipes ("||") for concatenation --it uses the plus ("+") sign.

    SQL Server 2012 introduced the CONCAT function as another alternative for concatenating strings.  One advantage of CONCAT, when understood) , is that it handles (eats) nulls automatically so that one doesn't have to explicitly do so when concatenating strings but expecting the non-null values to be returned:

    SELECT 'string' + NULL; --returns null
    SELECT CONCAT('string',NULL); -- returns 'string'
  • Awesome. I'm concatenating to a PK field, so I guess I can get away with either. Thank you again! 😀

  • Hey, sorry for the late reply. I did try out your method from SQL Server 2018 R.2 but because my initial column was an integer, it wasn't able to convert with the following message

    Microsoft SQL: Conversion failed when converting the varchar value 'A1-' to data type int.

    I googled a bit, and came up with the solution, SELECT CAST('A1-'+CustomerID as text) as CustomerID FROM Customers

    I know you have already been kind enough to confirm with me that a SELECT Statement does not affect the raw data, but just wanted to double check. Any argument within the SELECT Statement, whether it'd be CASE, CAST etc. will not affect the raw data, correct?

    • This reply was modified 3 months, 4 weeks ago by  Imrans23.
  • Perhaps the following but of study will convince you... 😀

    https://docs.microsoft.com/en-US/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15

    https://www.w3schools.com/sql/sql_select.asp

    https://www.techonthenet.com/sql_server/select.php

    https://sql-server-tutorial-blog.blogspot.com/2022/01/sql-server-select-statement.html

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Haha, thanks Jeff. Feel a bit better now writing these queries knowing everything will remain unchanged. Just a quick question. I tried the CAST in w3school's sandbox and it worked perfectly find.

    However, When I try it in the actual DB, it gives me the following error,

    explicit conversion from data type int to text is not allowed

    Any way I can get around this?

  • text datatype is deprecated -- don't use it.

    Cast as varchar(11) instead (11 is maximum # of characters in a negative int).

  • Hey Ratbak,

    Thanks again, I tried SELECT CAST('A1-'+CustomerID as text) as CustomerID FROM Customers

    Get the message. Conversion failed when converting the varchar value 'C25-' to data type int. Any help would be appreciated.

  • Imrans23 wrote:

    Hey Ratbak,

    Thanks again, I tried SELECT CAST('A1-'+CustomerID as text) as CustomerID FROM Customers

    Get the message. Conversion failed when converting the varchar value 'C25-' to data type int. Any help would be appreciated.

    TGhe error message is basically telling you exactly what is wrong.

    First of all, casting to a TEXT datatype is casting to an Out of Row LOB that can contain up to 2 billion characters AND it's been a deprecated datatype since 2005.

    Second, you have to convert numeric values to VARCHAR prior to trying to concatenate them using the "+" concatenation operator or use a special function like CONCAT, which isn't available in 2008.

    So, to do what you're trying to do and assuming that the CustomerID in the Customers table is an INT datatype, the code you need would look like the following...

    SELECT 'A1-'+ CONVERT(VARCHAR(10),CustomerID) as CustomerID FROM dbo.Customers

    Since you're new to T-SQL/SQL Server, please refer to the following MS documentation on Case and Convert.  These are two of the most important functions there are in T-SQL and I strongly recommend you take the time to study (no just read) all of that article and practice with some of the examples and then maybe look for 3rd part articles on the subject, as well.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

    Do an internet search for "Cast and Convert SQL Server" using your favorite search engine.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • As a bit of a sidebar, my personal preference for assigning alias names to columns is not to use "expression AS aliascolumnname".  I use aliascolumname = expression.

    Some people don't like that because it's "Not ANSI" and it's not "portable" across every bloody RDBMS there is.  You can stick with that limitation if you want but just about anything you do that's really powerful in an REDBMS isn't going to following ANSI standards and certainly won't be "portable".  If you're going to limit yourself that way, you should also throw away your scientific calculator and limit yourself to only a 4 function calculator. 😀

    The reason why I use the aliascolumname = expression format is because it keeps all the column names left aligned making them a whole lot easier to find and also allows me to vertically align code for a super easy  read.

    Here's some example code I'm working on for a friend of mine to demonstrate.

    --===== This creates integers from 0 thru 14
    SELECT DateSerial# = t.N
    FROM dbo.fnTally(0,14) t
    ;
    --===== Here, we directly convert those numbers to dates and dateparts.
    -- The resulting dates are of the DATETIME part
    SELECT DateSerial# = t.N
    ,TheDate = CONVERT(DATETIME,t.N)
    ,TheDayOfWeek = DATENAME(dw,CONVERT(DATETIME,t.N))
    ,TheDayOfWeek# = t.N%7+1 --Not good for dates before 19000101
    FROM dbo.fnTally(0,21) t
    ;
    --===== If we try to do the same DIRECT conversion using DATE or DATETIME2()...
    -- BOOM! We get an error because DATE and DATEIME2() is not ANSI/ISO
    -- compliant like DATETIME is, which allows all sorts of direct calculations.
    SELECT DateSerial# = t.N
    ,TheDate = CONVERT(DATE,t.N)
    ,TheDayOfWeek = DATENAME(dw,CONVERT(DATETIME,t.N))
    ,TheDayOfWeek# = t.N%7+1 --Not good for dates before 19000101
    FROM dbo.fnTally(0,21) t
    ;
    --===== If we actually want the DATE (or DATETIME2()) datatype,
    -- we can start with an actual converted Start Date...
    DECLARE @StartDate DATE = '19000101'
    SELECT DateSerial# = t.N
    ,TheDate = DATEADD(dd,t.N,@StartDate)
    ,TheDayOfWeek = DATENAME(dw,DATEADD(dd,t.N,@StartDate))
    ,TheDayOfWeek# = t.N%7+1 --Not good for dates before 19000101
    FROM dbo.fnTally(0,21) t
    ;
    --===== .. and we can do the same thing using a CROSS APPLY...
    SELECT DateSerial# = t.N
    ,TheDate = DATEADD(dd,t.N,v.StartDate)
    ,TheDayOfWeek = DATENAME(dw,DATEADD(dd,t.N,v.StartDate))
    ,TheDayOfWeek# = t.N%7+1 --Not good for dates before 19000101
    FROM dbo.fnTally(0,21) t
    CROSS APPLY (VALUES (CONVERT(DATE,'19000101')))v(StartDate)
    ;
    --===== ... or do the conversion on the fly with an limit for range
    -- of 01 Jan 1753 thru 31 Jan 9999.
    SELECT DateSerial# = t.N
    ,TheDate = CONVERT(DATE,DATEADD(dd,t.N,0))
    ,TheDayOfWeek = DATENAME(dw,CONVERT(DATE,DATEADD(dd,t.N,0)))
    ,TheDayOfWeek# = t.N%7+1 --Not good for dates before 19000101
    FROM dbo.fnTally(0,21) t
    ;

    I  call the format the "River Format" because it leaves a blank at character 8 for most things.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply