Unable to append strings to record from SELECT Statement

Question

Unable to append strings to record from SELECT Statement

Imrans23

Old Hand

Points: 339
More actions
January 20, 2022 at 8:43 pm

#3975390
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 2 years, 11 months ago by Imrans23.

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

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

ratbak Ten Centuries Points: 1381 More actions · Answer 1

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'

Imrans23 Old Hand Points: 339 More actions · Answer 2

Awesome. I'm concatenating to a PK field, so I guess I can get away with either. Thank you again! 😀

Imrans23 Old Hand Points: 339 More actions · Answer 3

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 2 years, 10 months ago by Imrans23.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)

Imrans23 Old Hand Points: 339 More actions · Answer 5

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?

ratbak Ten Centuries Points: 1381 More actions · Answer 6

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

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

Imrans23 Old Hand Points: 339 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)