Concatenate a symbol onto result

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

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenate a symbol onto result

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-02-11 : 12:27:15

Hello,

I have this code: In the result set I want the person_id field to be returned with { } around it. Does anyone know how to do that? Thanks in advance :).

DECLARE @run_id INT
SELECT @run_id = MAX(run_id) FROM ncsuds_runs;

SELECT '{'+p.person_id+'}',p.person_nbr, p.last_name, p.first_name, p.middle_name,
SUBSTRING(p.date_of_birth,5,2) + '/' + RIGHT(p.date_of_birth,2) + '/' + LEFT(p.date_of_birth,4) AS date_of_birth,
p.sex, p.grant_homeless, p.grant_farm, p.insured_ind, p.ssn,
i.income_group_id, ir.income_group_desc, ir.highest_percent_of_poverty_level,
dbo.ncsuds_GetRunStart(@run_id) AS start_date, dbo.ncsuds_GetRunEnd(@run_id) AS end_date
FROM ncsuds_tmp_person_with_encounter p
INNER JOIN ncsuds_tmp_income i ON p.person_id = i.person_id
INNER JOIN ncsuds_income_ref ir ON i.income_group_id = ir.income_group_id
;

Thanks in Advance!
Sherri

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-11 : 12:34:46

you need to convert the person_id to varchar to concatenate.

SELECT '{' + Convert(Varchar,p.person_id) + '}',.....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sross81
Posting Yak Master

228 Posts

Posted - 2009-02-11 : 12:36:58

Thank you for your response. I inputted the code you provided and I received this error. Is this a code error or database problem?

Msg 8170, Level 16, State 2, Line 5
Insufficient result space to convert uniqueidentifier value to char.

Here is how the code looks:

DECLARE @run_id INT
SELECT @run_id = MAX(run_id) FROM ncsuds_runs;

SELECT '{' + Convert(Varchar,p.person_id) + '}',p.person_nbr, p.last_name, p.first_name, p.middle_name,
SUBSTRING(p.date_of_birth,5,2) + '/' + RIGHT(p.date_of_birth,2) + '/' + LEFT(p.date_of_birth,4) AS date_of_birth,
p.sex, p.grant_homeless, p.grant_farm, p.insured_ind, p.ssn,
i.income_group_id, ir.income_group_desc, ir.highest_percent_of_poverty_level,
dbo.ncsuds_GetRunStart(@run_id) AS start_date, dbo.ncsuds_GetRunEnd(@run_id) AS end_date
FROM ncsuds_tmp_person_with_encounter p
INNER JOIN ncsuds_tmp_income i ON p.person_id = i.person_id
INNER JOIN ncsuds_income_ref ir ON i.income_group_id = ir.income_group_id
;

quote:
Originally posted by dinakar

you need to convert the person_id to varchar to concatenate.

SELECT '{' + Convert(Varchar,p.person_id) + '}',.....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Thanks in Advance!
Sherri

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-11 : 12:39:04

well looking at the column name I guessed the column is of int. The default of varchar is 32 I think. For GUIDS use higher value..

SELECT '{' + Convert(Varchar(50),p.person_id) + '}',.....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sross81
Posting Yak Master

228 Posts

Posted - 2009-02-11 : 12:40:56

I am sorry I should have said it was a Guid. That new code works great. That is great information to know. Thanks for sharing! :)

quote:
Originally posted by dinakar

well looking at the column name I guessed the column is of int. The default of varchar is 32 I think. For GUIDS use higher value..

SELECT '{' + Convert(Varchar(50),p.person_id) + '}',.....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Thanks in Advance!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources