SQLTeam.com | Weblogs | Forums

Concatenating a String with Itself in SQL

tsql

#1

I am trying to migrate a database from Sybase to SQL Server. The migration process works fine for the data. Within the database I have a number of existing stored procedures that have syntax where fields are concatenated to themselves in variables. Below is a simple example of the current code.
Within SQL server, the same query behaves differently. When I execute

(For both of these examples I do have the @ sign in front of the variables, I just cant copy it to this forum - it thinks they are users).

declare test1 char(60), part1 char(30), part2 char(30);
select part1 = 'String 1'
select part2 = 'String 2'
select test1 = part1
select test1 = test1 + part2
select test1
The result is: String1

Where did the string 2 go???

Making a simple change to the datatype of test1 to varchar all works well.
declare test1 varchar(60), part1 char(30), part2 char(30);
select part1 = 'String 1'
select part2 = 'String 2'
select test1 = part1
select test1 = test1 + part2
select test1
The result is String 1 String 2

However the problem is the database I am converting has 1000’s of stored procedures and I don’t what to have to manually go through every stored to modify the code from char to varchar – it would take weeks.
Is there any enviroment variable or something that can be set so that the original syntax still works?

Thanks for any help you can provide.


#2

Script the stored procedures into a text file. You can do the via the right click option of the database (generate scripts). Then do a find/replace for char. Run the script on the database to modify the stored procedures. Test test test.


#3

I have been known to go to the "Object Explorer Details" tab, select stored procedures that need change, right click and save to file the "CREATE" code. Then edit the file to change CREATE to ALTER and make the changes needed. WARNING! be careful when updating and as stated TEST.


#4

Thanks, that would save me a lot of time in fixing them. Do you happen to have any insight into why this is occurring? I'd also prefer not to change the existing stored procs if I have to - is there any setting that can be turned on/off to allow the current Stored Procs or is this just the way it works and I am going to have to change all the procs? Thanks again.


#5

Because Temp1 is CHAR it fills to 60 after the first select (select test1 = part1) so when you do the second select (select test1 = test1 + part2) it would produce a 90 character value but only the first 60 are kept.

See BOL for CHAR and VARCHAR


#6

There isn't a setting to change this. It's how CHAR data type works. It is filled with blanks if the string doesn't fill it. I almost never use CHAR.


#7

Yes I dont use Char either, this database was written a long time ago, but people no longer in our company, so we just have to commit to changing everything. Thanks.


#8

So to get rid of the blank spaces, just use rtrim and ltrim when adding the variables together.

ie select @test1 = ltrim(rtrim(@test1)) + ' ' + ltrim(rtrim(@part2))


#9

That's just a workaround. Fix the actual issue. The stored procedure code has to be changed. Easier to do find/replace on the data type than to change it to that, since the variables/columns in use will be named differently in the thousands of stored procedures.


#10

I never use CHAR either. I am sure there are some, but I never could find a situation where it is advantageous to use CHAR instead of VARCHAR. Admittedly, I abuse it sometimes; VARCHAR(1) :smile:

My guess is that any performance advantages gained from not having to compute the length etc. is more often than not negated by the extra storage taken up by CHAR, unless you are storing social security numbers of some such where the data is always guaranteed to be of fixed length.