Concatenate two text fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatenate two text fields

Author

Topic

JMikeF
Starting Member

5 Posts

Posted - 2006-03-29 : 11:18:44

New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:

Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....

I've created the following query that displays the columns:

CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE

FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID

WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE

Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?

Please advise - thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 11:20:05

why don't you simply do:
SELECT RQREQUIREMENTS.ID + ' ' + RQREQUIREMENTS.REQUIREMENTNAME, ...

Go with the flow & have fun! Else fight the flow

JMikeF
Starting Member

5 Posts

Posted - 2006-03-29 : 12:59:35

Spirit1,
Thanks for the advice, but it brings up another issue.
Here is the modded Select:
SELECT rp_admin_teds.RQREQUIREMENTS.ID + ' ' + rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME AS Expr1

But when I run this, I get this error:
Syntax error converting varchar value to a column of data type int.

I assume I need to DECLARE Expr1 as data type varchar. How would I do this? Thanks - JMike

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-29 : 13:03:44

U have to convert rp_admin_teds.RQREQUIREMENTS.ID to varchar

SELECT Convert(Varchar(100),rp_admin_teds.RQREQUIREMENTS.ID) + ' ' + rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME AS Expr1

Srinika

JMikeF
Starting Member

5 Posts

Posted - 2006-03-29 : 13:16:09

No luck, now getting syntax error near AS . . .

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 13:16:48

show us your whole query.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="weblogs.sqlteam.com/mladenp"]

JMikeF
Starting Member

5 Posts

Posted - 2006-03-29 : 13:21:25

SELECT CONVERT(Varchar(10), rp_admin_teds.RQREQUIREMENTS.ID + ' ' + rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME AS Expr1
FROM rp_admin_teds.RQREQUIREMENTS LEFT OUTER JOIN
rp_admin_teds.RQUSERDEFINEDFIELDVALUES ON
rp_admin_teds.RQREQUIREMENTS.ID = rp_admin_teds.RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (rp_admin_teds.RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 13:23:15

you forgot a )

SELECT CONVERT(Varchar(10), rp_admin_teds.RQREQUIREMENTS.ID) + ' ' + rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME AS Expr1
FROM rp_admin_teds.RQREQUIREMENTS LEFT OUTER JOIN
rp_admin_teds.RQUSERDEFINEDFIELDVALUES ON
rp_admin_teds.RQREQUIREMENTS.ID = rp_admin_teds.RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (rp_admin_teds.RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="weblogs.sqlteam.com/mladenp"]

JMikeF
Starting Member

5 Posts

Posted - 2006-03-29 : 13:30:29

That did it! Here what I ultimately used:

SELECT CONVERT(Varchar(100), rp_admin_teds.RQUSERDEFINEDFIELDVALUES.FIELDVALUE)
+ ' ' + rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME AS Expr1
FROM rp_admin_teds.RQREQUIREMENTS LEFT OUTER JOIN
rp_admin_teds.RQUSERDEFINEDFIELDVALUES ON
rp_admin_teds.RQREQUIREMENTS.ID = rp_admin_teds.RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (rp_admin_teds.RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

Now, to write the Expr1 output back to the RQREQUIREMENTS.REQUIREMENTNAME field - I would run an UPDATE query using the same FROM and WHERE statements. Here is the SET statement:

SET rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME =
CONVERT(Varchar(100), rp_admin_teds.RQUSERDEFINEDFIELDVALUES.FIELDVALUE)
+ ' ' + rp_admin_teds.RQREQUIREMENTS.REQUIREMENTNAME

Works perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources