Concatenating fields: concatenate

Showing posts with label concatenate. Show all posts

Sunday, February 12, 2012

Concatenation of two columns

Hi all,

I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.

Select First_Name + Last_Name as Name from Address;

How do i combine the two columns??

SQL 2000 running on Win 2000

Thanks in advance.This is only a guess, but:SELECT RTrim(first_name) + ',' + last_name
FROM Address-PatP|||Thank you for the prompt reply, it worked!

Concatenation of Text

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?Yes, UpdateText processes only one row per call.

The MDAC infrastructure was never meant to handle BLOBs, so trying to update a TEXT column in a million rows at once is really a bad idea. I'd either change the column datatype, or find a different way to acheive the same goal.

This just sounds like a problem sniffing eagerly at a new victim to me!

-PatP|||Thanks Pat. Unfortunately, I have to use a TEXT column because the data can exceed the 8k limit for VARCHAR and CHAR. I guess my choices are to use DTS and some sort of ActiveX script, write a script to concatenate before it hits the database, or do the unthinkable and write a cursor (although I don't really want to do that).

Dandy|||Nothing quite like being caught between the devil and the deep blue sea, is there? Given those choices, I'd opt for DTS.

Actually, cursors aren't logically bad, it is just that their performance is awful compared to set operations. Many databases like Oracle and Z-Series DB2 rely on cursors to do much of anything.

Be forewarned though, TEXT manipulation is slower than other datatypes. Just retrieving or storing a row with a TEXT or IMAGE column takes a lot longer than it does without those columns.

-PatP|||Of course it does, doh...First it needs to get the pointer (binary(16)), then retrieve 8K worth of data stored in a separate set of pages which results in at least 1 additional IO per row retrieved. BLOBs are wonderful (hehehe) when used for what they were invented, - not for set-based operations. And this is one of the few cases when a cursor may very well be applicable.

The MDAC infrastructure was never meant to handle BLOBs...Really? Who told you that?|||Really? Who told you that?It was either the guy who invented DTS, or the GPM for MS-SQL 7.0. They were both there, I just don't remember who actually said it and who just nodded sagely.

-PatP|||Oh, I see, and I'm an airplane :D|||I'm a teapot! I'm a teapot!

Concatenation of patient name question

I have a report built that returns patient information. My code to concatenate the patient name fields (last name, first name, middle name and surname) work fine unless the middle name or surname fields are null, then it returns the concatenated patient name field as null. The code is posted below. Is there an easy method to determine if the field is null and then apply the correct logic to concatenate the name with the elements that are not null?

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_mname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_sname))

as SRM_PatientName

Hello,

Yes, you can. This should work.

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_mname)), '')

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

Hope this helps.

Jarret

|||

Use the following expression,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

|||

I created a lot of reports with concatenated columns at least 20 in different combinations and all I needed was the CONVERT function. But you need ISNULL or COALESCE so here are some examples and the link for SQL Server Concatenation documentation for more options. Hope this helps.

COALESCE(a,'') + COALESCE(b,'')

ISNULL(a, ”) + ISNULL(b, ”)

http://msdn2.microsoft.com/en-us/library/ms177561.aspx

|||

Thanks Jarrett and Manivannan. I have adapted your examples and they're working great. I appreciate your helpl

Concatenation of integer data into text

I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.

If I could change the data type for the columns to varchar I think I could query them like this:

SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).

Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?

My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.

Casting should work.

It would be something like.

SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

|||

Hi Ryan: Thanks, that was so easy. Now I know how to cast.

How do I create a new column in the database into which the results of the query will automatically be inserted?

|||

I'm not sure exactly what you mean.

Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)

Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?

Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?

If you really need to do either the first option or second, I can point you toward how to do it.

|||

I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).

I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.

Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).

|||

Okay. If you really want the first option.

Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option

ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL

UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))

|||

Thanks Ryan. Exactly what I wanted in this instance.

Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?

|||

Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.

The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.

|||Thanks again Ryan.

concatenation of '0' + converted interger value into a string?

I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts the
integer, my results remain unchanged. I would appreciate any assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not nullHi Jeff,
You can strip the monthpart out of string representation of a date, that
will always include a leading zero when necessary, so you don't have to
worry about that, for example:
SELECT CONVERT(CHAR(2), trandate, 1)
FROM Offtable where trandate is not null
Style 1 with convert returns mm/dd/yy, and we are only interested in the
leftmost two characters, so a CHAR(2) will do.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
> I am unsure as to why I cannot concatenate '0' with the when '1' case
> statement below. Even thought the convert statement explicitly converts
the
> integer, my results remain unchanged. I would appreciate any
assistance...
>
> SELECT
> case LEN(datepart(m,trandate))
> when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
> when '2' then DATEPART(M,trandate)
> end
> FROM Offtable where trandate is not null
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
There I go again:
select
replace (str (datepart (mm, TranDate), 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OEKO955dDHA.1152@.TK2MSFTNGP11.phx.gbl...
You can rewrite the statement:
select
replace (str (TranDate, 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message =news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts =the
integer, my results remain unchanged. I would appreciate any =assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not null
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

There I go again:
select
replace (str =(datepart (mm, TranDate), 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau" = wrote in message news:OEKO955dDHA.1152=@.TK2MSFTNGP11.phx.gbl...
You can rewrite the =statement:
select
replace (str =(TranDate, 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Humphrey" wrote in message news:uYhK335dDHA.1636=@.TK2MSFTNGP12.phx.gbl...I am unsure as to why I cannot concatenate '0' with the when '1' =casestatement below. Even thought the convert statement explicitly converts theinteger, my results remain unchanged. I would appreciate =any assistance...SELECT case LEN(datepart(m,trandate)) when '1' then '0' =+ CONVERT(varchar(1),DATEPART(M, trandate)) =when '2' then DATEPART(M,trandate) end FROM Offtable where =trandate is not null

--=_NextPart_000_0181_01C37783.62DF5350--

concatenation Nullfields in T-SQL

Hi,
I have a list box for Address which shows available addresses for buildings.
I want the list box to concatenate the address and show it.
in Access I used to write :

Select [address] & " " & [city_] & " " & [state] & " " & [bldgzip] AS [Building Address] From tblAdrs

This would show the whole thing even if it had some Null fileds (it wouldn't show if I used + instead of &)

But I don't know why it won't do the same in T-SQL?

Can anyone help?on most servers Null + <any data type> = Null

you can disable this feature or use the ISNULL function.

Select isnull([address],'') + ' '+ isnull([city_],'') + ' '+ isnull([state],'') + ' '+ isnull([bldgzip],'') AS [Building Address] From tblAdrs|||Thanks Paul

Originally posted by Paul Young
on most servers Null + <any data type> = Null

you can disable this feature or use the ISNULL function.

Select isnull([address],'') + ' '+ isnull([city_],'') + ' '+ isnull([state],'') + ' '+ isnull([bldgzip],'') AS [Building Address] From tblAdrs

Concatenation Isssue (SELECT QUERY)

here is the sample data. I want a query which can fetch me a single record that can concatenate the value in the 3rd column for the same value in 1st column. do let me know if any understanding issue is there.

XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 2 arrier and DeltaV Controller is
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 3 nets and field equipment interface.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 1 This is the quote for RS3 migration

Thanks,
Rahul Jhasomething like SUM() for varchar data type.|||avast, you should be doing this in your application layer, me hearty

:)|||have just gt a code. wanted to share this with others, and looking forward for the comment from the forum.

-- Prepare sample data
DECLARE @.Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @.Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

SELECT * FROM @.Sample

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID|||but my database is 2000. not 2005. and in 2000 the above code will not work.|||Why not do it in the presentation layer/|||This here DBA needs to walk the plank.|||Arggggghhhh

shiver me timbers|||Your wood is cold..?
I just don't even want to know :p

...seriously, what doesthat phrase even mean?|||...seriously, what doesthat phrase even mean?haaaarr, ye be a pitiful excuse for a young pirate, me lad

http://en.wikipedia.org/wiki/Shiver_my_timbers|||hahaha its true its Pirate day today :) aaaaaarrrrrrr|||Arggggghhhh

shiver me timbers

Brett, Opie and Anthony fan ?

They had pirate talk today.|||buffett

He mentioned it last night at his concert at MSG|||buffett

He mentioned it last night at his concert at MSG

Rush Fan ? they played Monday night at MSG.|||Rush Fan ? they played Monday night at MSG.

In another lifetime|||Original Quote Posted By MCrowley.....
Why not do it in the presentation layer/

No I can't do this at the presentation layer. This issue has come duriong the data migration phase. It has to be done at the DB side only.........

Thanks,
Rahul Jha|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.

Actaully m normalising the DB. currently it's in De-normalised state......

Thanks,
Rahul Jha|||Ha!

Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=|||Chances are that you are de-normalising your data by concatenation.that's a bit tentative, isn't it george?

for sure it's denormalizing the data

:)|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.

DateField + TimeField

Bleugh.|||You can try this out...

"SELECT THIRD_COLUMN+FIRST_COLUMN FROM TABLE"

...it works if both are varchar type columns.
if you wanna add some special character inbetween ...

THIRD_COLUMN+'-'+FIRST_COLUMN will solve your purpose.|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.

DateField + TimeField

Bleugh.actually, that is "really concatenation" :)

what he wants to do is aggregation (a column operation over several rows)

it's a reasonable request

in MySQL, the GROUP_CONCAT aggregate function performs exactly this operation, with options for the separator and sequence of terms|||Ha!

Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=

How is it gonna help Georgy by any chance...........?? Have a look again on the query............. And if the link can help me to qrite the query then kindly guide me thru.........

Thanks,
Rahul Jha|||what he wants to do is aggregation (a column operation over several rows)

ur rgt.......

Thanks,
Rahul Jha|||start here:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html|||ur rgt.......

Thanks,
Rahul Jha

I love those phone commercials

omg wtf lol roflmao|||buffett

He mentioned it last night at his concert at MSG

You went all the way from the Channel Islands to Madison Square Gardens?! Wow, you are a serious Jimmy Buffett fan!! :D|||ur rgt.......OMG, is there a new instance of The Great Bangalore Alphabet Famine of 1978 (www.nevermind-Im-just-kidding) going on?

Horrors.

Fortunately, relief packages of 1000 letters are available on a first-come-first-served basis at the CIIL (http://www.ciil.org/)|||he is debugging his app on a cell phone while driving

using only his thumb

actually, i will cut him a lot of slack because of this

:cool:

Concatenation in stored procedure

hi everyone
i am writing a stored procedure and i am new in this field
i want to concatenate a string, i got an error
hope someone could help.
my stored procedure is:
CREATE PROCEDURE dbo.ProdCatComp
(
@.Product nvarchar(40),
)
AS
DECLARE @.str nvarchar(100)
SET @.str='Products.ProductName like '%' + @.Product + '%''

SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.HomePage
FROM Products INNER JOIN
Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName<>'' AND @.str
GO

The error is :
Error 403: Invalid operator for data type. Operator equals modulo, type equals varchar.you have to make the whole query dynamic
and then use exec/sp_executesql|||luber is correct in most cases you will need to build a sql string and then execute it, but in this particular case the code below should work - not tested.


CREATE PROCEDURE dbo.ProdCatComp
 @.Product nvarchar(40)
AS DECLARE @.str nvarchar(100)
 SET @.str = '%' + @.Product + '%'
 SELECT Products.ProductName,
 Products.UnitPrice,
 Categories.CategoryName,
 Suppliers.CompanyName,
 Suppliers.ContactName,
 Suppliers.HomePage
 FROM Products
 INNER JOIN Suppliers
 ON Products.SupplierID = Suppliers.SupplierID
 INNER JOIN Categories
 ON Products.CategoryID = Categories.CategoryID
 WHERE Products.ProductName <> ''
 AND Products.ProductName LIKE @.str
GO

Concatenation headache SQL Server 2005 Express

ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' + tbl_Employees.FirstName
As WDS FROM tbl_Employees"
I am trying to concatenate two fields in SQL Server Express 2005. Employees
number is int (Number) and Employees Firstname is varchar(String)
I get the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Jason' to data type
int.
Please help... Thanks very much in advance> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
It seems your EmployeeNumber column is an int. Because int has a higher
data type precedence than nvarchar, FirstName is implicitly converted to int
and this fails because you have FirstName values that are not integers.
You can explicitly cast EmployeeNumber to nvarchar in order to perform
concatenation instead of addition and avoid the conversion error:
SELECT
CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
N' ' +
tbl_Employees.FirstName As WDS
FROM dbo.tbl_Employees
However, I suggest you do this concatenation in your application code rather
than SQL Server. Formatting data for display purposes is a task better done
in the presentation layer.
See the data type precedence topic in the SQL Server Books Online for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>|||Great!!! Worked!!! Thanks for your help and insight. I appreciate it
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6EC71182-59F9-4780-B6DE-E64BA2AA4D64@.microsoft.com...
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
> It seems your EmployeeNumber column is an int. Because int has a higher
> data type precedence than nvarchar, FirstName is implicitly converted to
> int and this fails because you have FirstName values that are not
> integers. You can explicitly cast EmployeeNumber to nvarchar in order to
> perform concatenation instead of addition and avoid the conversion error:
> SELECT
> CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
> N' ' +
> tbl_Employees.FirstName As WDS
> FROM dbo.tbl_Employees
> However, I suggest you do this concatenation in your application code
> rather than SQL Server. Formatting data for display purposes is a task
> better done in the presentation layer.
> See the data type precedence topic in the SQL Server Books Online for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>> Please help... Thanks very much in advance
>>
>>
>|||If you want to return a numeric value concatenated with alpha data implicit
conversions fail. So you need to convert() or cast(), both are very similar
although I prefer convert as you can explicitly define the full data type
including the length. I've included an example below.
SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
OR
SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
NOTE: you need to define the length of the nvarchar, which is typically the
length of the number.
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>|||Thanks very much... appreciate your help
> NOTE: you need to define the length of the nvarchar, which is typically
> the length of the number.
What if the length of nvarchar is unknown?
"D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
> If you want to return a numeric value concatenated with alpha data
> implicit conversions fail. So you need to convert() or cast(), both are
> very similar although I prefer convert as you can explicitly define the
> full data type including the length. I've included an example below.
> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
> OR
> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
>
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>> Please help... Thanks very much in advance
>>
>>
>|||Or, better yet, do the conversion on the client. It will help your query run
faster (as the SQL engine does not have to do the conversions and
concatenation).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
www.hitchhikerguides.net
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thanks very much... appreciate your help
>> NOTE: you need to define the length of the nvarchar, which is typically
>> the length of the number.
> What if the length of nvarchar is unknown?
> "D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
> news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
>> If you want to return a numeric value concatenated with alpha data
>> implicit conversions fail. So you need to convert() or cast(), both are
>> very similar although I prefer convert as you can explicitly define the
>> full data type including the length. I've included an example below.
>> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>> OR
>> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>>
>> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
>> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data
>> type int.
>> Please help... Thanks very much in advance
>>
>>
>|||Thanks...
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:000D6EA4-77BC-4D8E-8A41-1D52639C99B4@.microsoft.com...
> Or, better yet, do the conversion on the client. It will help your query
> run faster (as the SQL engine does not have to do the conversions and
> concatenation).
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> www.hitchhikerguides.net
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Thanks very much... appreciate your help
>> NOTE: you need to define the length of the nvarchar, which is typically
>> the length of the number.
>> What if the length of nvarchar is unknown?
>> "D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
>> news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
>> If you want to return a numeric value concatenated with alpha data
>> implicit conversions fail. So you need to convert() or cast(), both are
>> very similar although I prefer convert as you can explicitly define the
>> full data type including the length. I've included an example below.
>> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>> OR
>> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>>
>> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
>> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data
>> type int.
>> Please help... Thanks very much in advance
>>
>>
>>
>

Concatenation headache SQL Server 2005 Express

Concatenation getting truncated

Hello,

Using SQL SERVER 2000

I have 4 columns with varchar(80) each that I want to concatenate.
When I look at the result, it only gives me 256 characters. What am I
missing on my code?

Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +
char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+
rtrim(line4))
>From tableOne
Go

Thank you for your input.

EdgarEdgar (edgarjtan@.yahoo.com) writes:
> Using SQL SERVER 2000
> I have 4 columns with varchar(80) each that I want to concatenate.
> When I look at the result, it only gives me 256 characters. What am I
> missing on my code?
> Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +
> char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+
> rtrim(line4))
>>From tableOne
> Go

Probably nothing. If you are using Query Analyzer, look under
Tools->Options->Results->Maximum Characters per Column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 23 May 2006 21:09:32 -0700, Edgar wrote:

>Hello,
>Using SQL SERVER 2000
>I have 4 columns with varchar(80) each that I want to concatenate.
>When I look at the result, it only gives me 256 characters. What am I
>missing on my code?
>Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +
>char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+
>rtrim(line4))
>>From tableOne
>Go
>Thank you for your input.
>Edgar

Hi Edgar,

Query Analyzer is displaying only part of the results.

Click "Tools" / "Options" and activate the "Results" tab. Increase the
"Maximum characters per column" to at least 320. Now rerun the query.

--
Hugo Kornelis, SQL Server MVP|||Thank you so much, Mr. Kornelis and Mr.Sommarskog, for your direction.

Now I can see all the data on my concatenated column.

Edgar J.

concatenation but with a format...

Hi Consider a table with a decimal and a string varibale.
Dec String
3 fred
23 bill
I need to concatenate them but with leading zeros such as
03Fred
23Bill
Ideas please ??
GerryAs long as you don't need to cope with negative numbers, you can get by with the simple solution of:SELECT Replace(Str([Dec], 2), ' ', '0')
FROM dbo.myTable-PatP|||drop table #tmp
create table #tmp(id int,col1 varchar(10))
go
insert #tmp(id,col1) values(1,'a')
insert #tmp(id,col1) values(11,'b')
insert #tmp(id,col1) values(111,'c')
insert #tmp(id,col1) values(1111,'d')
insert #tmp(id,col1) values(11111,'e')
insert #tmp(id,col1) values(23,'f')
go
select replicate('0',5-len(cast(id as varchar)))+cast(id as varchar)+' '+col1 from #tmp|||Both worked nicely. Thanks a lot

Gerry

concatenation

I am trying to concatenate a long string together ad it keeps truncating how
can I extend the length that is allowed to truncate?
TIAHow do you it is being truncated?
Can you post the code?
AMB
"JMNUSS" wrote:

> I am trying to concatenate a long string together ad it keeps truncating h
ow
> can I extend the length that is allowed to truncate?
> TIA|||maybe truncated wasn't the correct term. The string is being cut off at the
end...
What I want to see is something like "this<>is<> a<>long<> string"
What is get is "this<> is<> a<> lon"
and that's it
"Alejandro Mesa" wrote:
> How do you it is being truncated?
> Can you post the code?
>
> AMB
> "JMNUSS" wrote:
>|||How long a string?
Where are you doing the concatenation?
Show us some code.
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:061DB1FF-1529-45B7-B51E-8627A6162936@.microsoft.com...
> I am trying to concatenate a long string together ad it keeps truncating
how
> can I extend the length that is allowed to truncate?
> TIA|||No, you can not do that. You can insert the result of the sp into a table an
d
then process the table, or you can rewrite the sp as a table-valued user
defined function, or you can call the sp using rowset function.
Example:
use northwind
go
create table #t (
Shippeddate datetime,
OrderID int,
Subtotal money,
col_Year int
)
insert into #t
execute dbo.[Sales by Year] '19960101', '19961231'
select
*
from
#t
where
Subtotal between 1200.00 and 1500.00
drop table #t
go
AMB
"JMNUSS" wrote:

> I am trying to concatenate a long string together ad it keeps truncating h
ow
> can I extend the length that is allowed to truncate?
> TIA|||Are you viewing this in Query Analyser?
If so: Tools/Options/Results and change the "Maximum characters per column"
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:B1036631-B165-4251-AF5B-A6C3777C0705@.microsoft.com...
> maybe truncated wasn't the correct term. The string is being cut off at
the
> end...
> What I want to see is something like "this<>is<> a<>long<> string"
> What is get is "this<> is<> a<> lon"
> and that's it
> "Alejandro Mesa" wrote:
>
truncating how|||Sorry.
AMB
"Alejandro Mesa" wrote:
> No, you can not do that. You can insert the result of the sp into a table
and
> then process the table, or you can rewrite the sp as a table-valued user
> defined function, or you can call the sp using rowset function.
> Example:
> use northwind
> go
> create table #t (
> Shippeddate datetime,
> OrderID int,
> Subtotal money,
> col_Year int
> )
> insert into #t
> execute dbo.[Sales by Year] '19960101', '19961231'
> select
> *
> from
> #t
> where
> Subtotal between 1200.00 and 1500.00
> drop table #t
> go
>
> AMB
>
> "JMNUSS" wrote:
>|||> What is get is "this<> is<> a<> lon"
Where do you get this, in your client app?
If you are using SQL Query Analyzer and the result is being truncated, go to
tools - Options - Results and change "Maximum characters per column:" (max
number is 8192).
AMB
"JMNUSS" wrote:
> maybe truncated wasn't the correct term. The string is being cut off at t
he
> end...
> What I want to see is something like "this<>is<> a<>long<> string"
> What is get is "this<> is<> a<> lon"
> and that's it
> "Alejandro Mesa" wrote:
>|||Heh Alejandro, get in the right thread...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:58BD3B7D-A23E-44BC-9008-4276A8B7DE58@.microsoft.com...
> No, you can not do that. You can insert the result of the sp into a table
and
> then process the table, or you can rewrite the sp as a table-valued user
> defined function, or you can call the sp using rowset function.
> Example:
> use northwind
> go
> create table #t (
> Shippeddate datetime,
> OrderID int,
> Subtotal money,
> col_Year int
> )
> insert into #t
> execute dbo.[Sales by Year] '19960101', '19961231'
> select
> *
> from
> #t
> where
> Subtotal between 1200.00 and 1500.00
> drop table #t
> go
>
> AMB
>
> "JMNUSS" wrote:
>
how

concatenation

How can I concatenate text with parameter?
Until now, I was making two texboxes...Make the value of the textbox an expression. Right mouse click on the
textbox, pick expression. Brings you to the expression builder. In the
expression build put in the text and ampersand and then your parameter. You
should end up with something like this:
="Some text goes here " & Parameters!MyParameterWhichIsCaseSensitive.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jo?ko ?ugar" <josko_bla@.netgen_rem0ve_this_and_bla.hr> wrote in message
news:d7uu7o$8er$1@.sunce.iskon.hr...
> How can I concatenate text with parameter?
> Until now, I was making two texboxes...|||HI Bruce,
Do you know id there is a way to remove trailing spaces in a First_Name field.
When I concatenation First and Last, I end up with "Bob Hansen".
I guess it must be in the databse with trailing spaces... but I don't know
why.
I'll try to check it and see if that is tha case.
I was looking for a truncation function to strip off the spaces.
Any ideas'
THANKS!!! Bob Hansen
--
Robert Hansen
"Bruce L-C [MVP]" wrote:
> Make the value of the textbox an expression. Right mouse click on the
> textbox, pick expression. Brings you to the expression builder. In the
> expression build put in the text and ampersand and then your parameter. You
> should end up with something like this:
> ="Some text goes here " & Parameters!MyParameterWhichIsCaseSensitive.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JoÅ¡ko Å ugar" <josko_bla@.netgen_rem0ve_this_and_bla.hr> wrote in message
> news:d7uu7o$8er$1@.sunce.iskon.hr...
> > How can I concatenate text with parameter?
> > Until now, I was making two texboxes...
>
>|||You should be able to use string.trim() class. Try this
=trim(firstname) & " " & trim(lastname)|||You should be able to use string.trim() class. Try this
=trim(firstname) & " " & trim(lastname)|||THANKS Tosonali!!
after I the posted question I found where the vb functions are applicable to
the Report Designer..... I didn't know
Thanks again!
--
Robert Hansen
"tosonali" wrote:
> You should be able to use string.trim() class. Try this
> =trim(firstname) & " " & trim(lastname)
>

Concatenation

SQL Server 2000
I'm having a lot of grief trying to concatenate additional text to a text
column and would appreciate some help.
Here's some sample statements:
CREATE TABLE Test (s TEXT)
INSERT INTO TEST(s) VALUES ('The cat')
UPDATE TEST SET s = s + ' on the mat'
The last line above results in:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've tried everything I can think of. Casting or converting the literal to
TEXT returns the same error and:
UPDATE TEST SET s = {fn CONCAT(s, ' on the mat')}
results in:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals concatenation, type equals
text.
Help!
TIA,
Geofflook at readtext and writetext and the update statement for sql server 05
with .WRITE
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97DBBE37BA208gjctcswxnsrt@.207.46.248.16...
> SQL Server 2000
> I'm having a lot of grief trying to concatenate additional text to a text
> column and would appreciate some help.
> Here's some sample statements:
> CREATE TABLE Test (s TEXT)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST SET s = s + ' on the mat'
> The last line above results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
> I've tried everything I can think of. Casting or converting the literal to
> TEXT returns the same error and:
> UPDATE TEST SET s = {fn CONCAT(s, ' on the mat')}
> results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals concatenation, type equals
> text.
> Help!
> TIA,
> --
> Geoff|||Try this instead:
CREATE TABLE Test (s text)
INSERT INTO TEST(s) VALUES ('The cat')
UPDATE TEST
SET s = cast(s as varchar(50)) + ' on the mat'
You can insert a varchar into a text but cannot use the + for a text
field.
Geoff Lane wrote:
> SQL Server 2000
> I'm having a lot of grief trying to concatenate additional text to a text
> column and would appreciate some help.
> Here's some sample statements:
> CREATE TABLE Test (s TEXT)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST SET s = s + ' on the mat'
> The last line above results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
> I've tried everything I can think of. Casting or converting the literal to
> TEXT returns the same error and:
> UPDATE TEST SET s = {fn CONCAT(s, ' on the mat')}
> results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals concatenation, type equals
> text.
> Help!
> TIA,
> --
> Geoff|||"Gary Gibbs" <ggibbs@.aahs.org> wrote in news:1149702816.828060.156320
@.i40g2000cwc.googlegroups.com:

> Try this instead:
> CREATE TABLE Test (s text)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST
> SET s = cast(s as varchar(50)) + ' on the mat'
>
> You can insert a varchar into a text but cannot use the + for a text
> field.
Thanks a million.
FWIW, I've just tried:
CAST(s AS VARCHAR) + ' on the mat'
and it worked. This is good! s is a text column because it's a "comment"
field that I expect to occasionally grow to over a couple of thousand
characters.
Thanks again,
Geoff|||There is another (and probably better) way to accomplish this. Check
BOL about UPDATETEXT.
Gary Gibbs wrote:
> Try this instead:
> CREATE TABLE Test (s text)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST
> SET s = cast(s as varchar(50)) + ' on the mat'
>
> You can insert a varchar into a text but cannot use the + for a text
> field.
> Geoff Lane wrote:|||> FWIW, I've just tried:
> CAST(s AS VARCHAR) + ' on the mat'
> and it worked. This is good! s is a text column because it's a "comment"
> field that I expect to occasionally grow to over a couple of thousand
> characters.
Some comments.
(1) always specify a length for VARCHAR(). CAST(s AS VARCHAR(2048)) for
example is much more reliable and predictable than CAST(s AS VARCHAR). In
some circumstances you will get a VARCHAR(1) and in others you will get a
VARCHAR(30). If you run that update statement against a column that has
more than 30 characters, I think you will see truncation (but haven't tested
it). Just always specify a length.
(2) if you are only expecting a couple thousand characters, then I suggest
using VARCHAR(4000) or VARCHAR(8000). N/VARCHAR(MAX) in SQL 2005 is much
easier to work with, and doesn't have quite as many limitations as
TEXT/NTEXT, but should still be reserved for required usage.
A|||Beware: casting as a VARCHAR without specifying a size defaults to 30
characters. If your s column contains values larger than 30 characters,
they will be truncated. I.e.:
DECLARE @.s CHAR(4000)
SELECT @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLM
NOPQRSTUVWXYZ'
SELECT @.s, LEN(@.s) -- 52 characters
SELECT CAST(@.s AS VARCHAR), LEN(CAST(@.s AS VARCHAR)) -- truncates to 30
characters
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97DBC1F645AB4gjctcswxnsrt@.207.46.248.16...
> "Gary Gibbs" <ggibbs@.aahs.org> wrote in news:1149702816.828060.156320
> @.i40g2000cwc.googlegroups.com:
>
> Thanks a million.
> FWIW, I've just tried:
> CAST(s AS VARCHAR) + ' on the mat'
> and it worked. This is good! s is a text column because it's a "comment"
> field that I expect to occasionally grow to over a couple of thousand
> characters.
> Thanks again,
> --
> Geoff|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:ePFgZ4liGHA.3848@.TK2MSFTNGP04.phx.gbl:

> Some comments.
> (1) always specify a length for VARCHAR(). CAST(s AS VARCHAR(2048))
> for example is much more reliable and predictable than CAST(s AS
> VARCHAR). In some circumstances you will get a VARCHAR(1) and in
> others you will get a VARCHAR(30). If you run that update statement
> against a column that has more than 30 characters, I think you will
> see truncation (but haven't tested it). Just always specify a length.
Yep - you're correct and I posted too soon. Shortly after posting I
discovered that my text column was being truncated before the
concatentation and I've ended up using VBScript in the calling ASP to
kludge my way around it viz:
strQuery = "SELECT s FROM myTable WHERE tID=" & tID
rs.Open strQuery, conn
strS = rs("s")
rs.Close
strQuery = "UPDATE myTable " & _
"SET s = '" & strS & " on the mat' " & _
"WERE tID=" & tID
conn.Execute strQuery
Yes, I know that it's messy and hits the database too many times - but it
works. The application is fairly lightly loaded so the performance hit
isn't too drastic. That said, I intend investigating Gary's suggestions
tomorrow - although I am limited to SQL Server 2000 until at least the
end of this year and so can't use anything introduced with 2005!

> (2) if you are only expecting a couple thousand characters, then I
> suggest using VARCHAR(4000) or VARCHAR(8000). N/VARCHAR(MAX) in SQL
> 2005 is much easier to work with, and doesn't have quite as many
> limitations as TEXT/NTEXT, but should still be reserved for required
> usage.
I'm expecting the field length to regularly exceed 2000 characters but a
user might want to write almost their life story there. For that reason,
I don't want to limit the field length (although they'd need to go some
to break VARCHAR(8000) !) Perhaps the introduction of VARCHAR(MAX) is the
catalyst for conversion to 2005 :)
Thanks to all,
Geoff|||> Beware: casting as a VARCHAR without specifying a size defaults to 30
> characters.
And in other cases, 1 character, for example DECLARE.
DECLARE @.foo VARCHAR;
SET @.foo = 'abc';
SELECT @.foo;
A|||True. I was talking just about CASTing a value, not the DECLARE statement,
since he's casting from a TEXT column.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uxFcqFmiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> And in other cases, 1 character, for example DECLARE.
> DECLARE @.foo VARCHAR;
> SET @.foo = 'abc';
> SELECT @.foo;
> A
>

Friday, February 10, 2012

Concatenating strings

I have a table with seven different descriptor fields, and at times it's
necessary to concatenate all seven into a single field in a query. I tried
simply concatenating the fields together, but if a single input field has a
null the output is a null. It does this even though the
concat_null_yields_null property is set to false, which will never cease to
mystify me.
Regardless, I can probably get the results I want by writing an extensive
CASE statement, but was wondering if there was a simpler method. I'm
wondering if there's a similar function to COALESCE -- instead of returning
the first non-null field I want to return all non-null fields. Either that o
r
is there some other reason besides concat_null_yields_null why null input
would return null using simple string concatenation.
Thanks in advance.Use function ISNULL.
Example.
select 'SQL ' + isnull(cast(null as varchar(25), 'Server')
go
AMB
"mike" wrote:

> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease t
o
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of returnin
g
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.|||You need to say:
SELECT COALESCE(col1, '')+COALESCE(col2, '')+...+COALESCE(colN, '') ...
Or, do the concatenation at the client/presentation tier.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:594CA1BB-46BB-4A34-9C5C-D4EEBB4B9985@.microsoft.com...
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease
to
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of
returning
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.

Concatenating Numeric Fields

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

I think I found it...Something like this works..

SELECT *, CAST(vehFacID AS VARCHAR(4)) + '-' + CAST(vehID AS VARCHAR(10)) AS vehCombo FROM Vehicles

Is this the right way to do this?

J.H.

|||

If the vehFacId and vehId are numbers, then this is the way to go.

<stuff you can ignore if you want>

A bit nasty with the column names, I hope for your sake you don't have 3 letter abbreviations in every column (but not in your table name.) That must be hard to follow.

</stuff you can ignore if you want>

|||

Are you referring to the "veh" abbreviation? If so, why would you say it would be hard to follow? A small sample of my tables is like:

Vehicles, Departments, Facilities, Customers, etc...I use the 3 (or 4 sometimes) letter abbreviation to determine which table the field came from. I am open to hearing a better suggestion if you have one.

J.H.

|||

You know what else I am curious about is the casting. My numeric columns in this case are smallInt and can hold up to 5 digits. Is the recommendation to cast them to varchar(5) in this case?

J.H.

|||

I don't see any problem even if you cast to varchar(25), that way down the road if you happen to change the datatype from smallint to int, you don't have to worry about T-SQL code like this in various stored procs and functions.

As far as database naming conventions goes there isn't a standard. I wish Microsoft would have suggested something on MSDN.

I kind of agree with a article on aspfaq: http://www.aspfaq.com/show.asp?id=2538

|||

A little bit for the veh abbreviation. I would prefer to see vehicleId, and vehicleFaciltiyId, etc, which is easier to follow for the uninitiated (and in fact good finger exercises :)

The vehFacId was what kind of concerned me. I got this flash of:

select vehId, mak, modYr, numWhl, vehIdNum...etc.

There were a lot of these sorts of naming conventions back when names could only be 30 characters (funny how many times we hit 30, but rarely do I go over it now...) I don't like to see something that might be an issue and not say something. (hence the: <stuff you can ignore if you want> tags) Like the link to aspfaq says, it is a matter of taste, but the more clear it is, the more clear it is.

If a new person or contractor or newsgroup helper can read it and understand it, your job of naming is done right.

concatenating multiple results to a string?

I am not sure if this is possible but here is what i need. Is it possible to
query the database and concatenate the results into a string? for instance i
f
i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
and it returned order1,order2,order3,order4 - the returned values are what i
needed concatenated as on string.
Regards,
chrisThis is a common newbie mistake, from working with file systems that
were part of the application language. In a tiered architecture,
display functions are done in the front end and not in the database.
The kludge, if you do not want to be a good programmer, is to use a
cursor to concatenate the string. Slow, not portable and something of
a probelm to maintain, but it will work.|||You can pull the data into an Excel pivot table.
"chris" <chris@.discussions.microsoft.com> wrote in message
news:A015A4BE-A46E-40B1-933F-4787A73E9267@.microsoft.com...
> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||I realize it should be handled on the front end but thats not possible. My
company has an ERP system that uses an outdated report writer. It will allow
me to do an "extended query" to the database for one record only. I was
hoping to concatenate my returned rows by exec a SP and pulling that into th
e
report.
"--CELKO--" wrote:

> This is a common newbie mistake, from working with file systems that
> were part of the application language. In a tiered architecture,
> display functions are done in the front end and not in the database.
> The kludge, if you do not want to be a good programmer, is to use a
> cursor to concatenate the string. Slow, not portable and something of
> a probelm to maintain, but it will work.
>|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"chris" wrote:

> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||Let's assume you have a query with multiple records for each employee (one
record for each phone number):
Fred 555-555-0235
Fred 555-555-9124
Sue 555-555-0133
Now you want to roll up all the employee records so that that the phone
numbers are a comma delimited list like so:
Fred 555-555-0235, 555-555-9124
Sue 555-555-0133
You can select the employee list into a temporary table and then select a
distinct employee list with null PhoneList into a 2nd temporary table. Once
done, use a cursor to loop through the 1st table and update the PhoneList
column on the 2nd table using the EmployeeID. If anyone knows of a set based
query / update that will achieve the same then please post. Otherwise, don't
knock it.
declare Employees cursor for
select
EmployeeID,
Phone
from
#employees
open Employees
fetch Employees into
@.EmployeeID,
@.Phone
while (@.@.fetch_status = 0)
begin
update
#PhoneCombined
set
PhoneList = PhoneList +
case
when PhoneList = '' then @.Phone
when PhoneList like '%' + @.Phone + '%' then ''
else ', ' + @.Phone
end
where
EmployeeID = @.EmployeeID
fetch Employees into
@.EmployeeID,
@.Phone
end
close Employees
deallocate Employees
drop table #employees
"chris" <chris@.discussions.microsoft.com> wrote in message
news:A015A4BE-A46E-40B1-933F-4787A73E9267@.microsoft.com...
> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||Test this:
DECLARE @.v CHAR(255)
SET @.v = ''
SELECT @.v = LTRIM(RTRIM(@.v)) + CONVERT(CHAR, Order_Id) FROM Customer_Orders
WHERE Customer = 'Customer A'
SELECT @.v
GO
Mihaly
"chris" wrote:

> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||Here is other method
Declare @.s nvarchar(255)
select
@.s=coalesce(@.s+','+convert(varchar(10),o
rder_Id),convert(varchar(10),order_I
d))
from Customers_Orders
Where Customer = 'Customer A'
select @.s
Madhivanan|||And what if the result is expected to be very long (too long for
nvarchar)? I understand I can't declare a TEXT variable
madhivanan2001@.gmail.com wrote:
> Here is other method
> Declare @.s nvarchar(255)
> select
> @.s=coalesce(@.s+','+convert(varchar(10),o
rder_Id),convert(varchar(10),order
_Id))
> from Customers_Orders
> Where Customer = 'Customer A'
> select @.s
> Madhivanan
>|||All the more reason to handle the concatenation where it belongs: one the
client/presentation tier.
On 2/27/05 10:55 AM, in article OaoWUROHFHA.1392@.TK2MSFTNGP10.phx.gbl, "Uri
Dor" <tablul@.newsgroups.nospam> wrote:
> And what if the result is expected to be very long (too long for
> nvarchar)? I understand I can't declare a TEXT variable
> madhivanan2001@.gmail.com wrote:

Concatenating Fields - Null Problem

I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
one of the fields is null, the whole value comes out at null. Can anyone
give me a hint on how to still show fld1 and fld2 if fld3 is null?
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemPut coalesce or ISNULL around your fields
select coalesce(fld1,'') + coalesce(fld2,'') + cpalesce(fld3,'') as BigField
from table
this is for character data
for ints use this
select coalesce(fld1,0) + coalesce(fld2,0) + cpalesce(fld3,0) as BigField
from table
http://sqlservercode.blogspot.com/
"chuckdfoster" wrote:

> I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when an
y
> one of the fields is null, the whole value comes out at null. Can anyone
> give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||SELECT COALESCE(col1,'')+COALESCE(col2,'')+COAL
ESCE(col3,'') FROM whatever
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||The IsNull() function returns an alternate value when the supplied value is
NULL.
isnull(fld1,'') + isnull(fld2,'') + isnull(fld3,'')
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Hi,
Try using IsNull function.
Example: IsNull(fld1,'Null')
If fld1 is null, it will be replaced with string 'Null'.
--
*** Sent via Developersdex http://www.examnotes.net ***|||hi "chuckdfoster",
hope this helps
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All
expressions must be of the same type or must be implicitly convertible to th
e
same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
..
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
Examples
In this example, the wages table is shown to include three columns with
information about an employee's yearly wage: hourly_wage, salary, and
commission. However, an employee receives only one type of pay. To determine
the total amount paid to all employees, use the COALESCE function to receive
only the nonnull value found in hourly_wage, salary, and commission.
SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO
Here is the result set:
Total Salary
--
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000
(12 row(s) affected)
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"chuckdfoster" wrote:

> I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when an
y
> one of the fields is null, the whole value comes out at null. Can anyone
> give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Try
SELECT
ISNULL(fld1,'') + ISNULL(fld2,'')+ISNULL(fld3,'')
FROM YourTable
You can also use the COALESCE function instead of ISNULL.
If you are interested in the differences, have a look at
http://toponewithties.blogspot.com/...es.blogspot.com
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Thanks,
That worked perfect. I knew there had to be an easy way.
Thanks,
Chuck Foster
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:C2F6C12E-56AC-4C5C-9E4D-2AF873081BBD@.microsoft.com...
> Put coalesce or ISNULL around your fields
> select coalesce(fld1,'') + coalesce(fld2,'') + cpalesce(fld3,'') as
> BigField
> from table
> this is for character data
> for ints use this
> select coalesce(fld1,0) + coalesce(fld2,0) + cpalesce(fld3,0) as BigField
> from table
>
> http://sqlservercode.blogspot.com/
> "chuckdfoster" wrote:
>

Sunday, February 12, 2012

Concatenation of two columns

Concatenation of Text

Concatenation of patient name question

Concatenation of integer data into text

concatenation of '0' + converted interger value into a string?

concatenation Nullfields in T-SQL

Concatenation Isssue (SELECT QUERY)

Concatenation in stored procedure

Concatenation headache SQL Server 2005 Express

Concatenation headache SQL Server 2005 Express

Concatenation headache SQL Server 2005 Express

Concatenation getting truncated

concatenation but with a format...

concatenation

concatenation

Concatenation

Friday, February 10, 2012

Concatenating strings

Concatenating Numeric Fields

concatenating multiple results to a string?

Concatenating Fields - Null Problem

Concatenating fields

Blog Archive

About Me