Concatenation

Question

Concatenation

achowe

SSCrazy

Points: 2075
More actions
August 15, 2003 at 4:25 am

#82216

I have a field which is constructed of 4 other fields, all of value char. Below is the SQL statement Ive been using to format the field (called cust_id):
update clnpers set cust_id = shop_id + rec_sname + rec_fname + rec_bday
However, when the table is populated with data each field seems to have at least a 10 space padding at the end, adding spaces inside the cust_id field.
Ive used the LEN function to test the length of each field but this doesnt show the padding. Any idea what could be the problem?
Cheers guys!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Viewing 15 posts - 1 through 15 (of 24 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Andrew,

quote:
I have a field which is constructed of 4 other fields, all of value char. Below is the SQL statement Ive been using to format the field (called cust_id):
update clnpers set cust_id = shop_id + rec_sname + rec_fname + rec_bday

what data type is cust_id ?

quote:
However, when the table is populated with data each field seems to have at least a 10 space padding at the end, adding spaces inside the cust_id field.
Ive used the LEN function to test the length of each field but this doesnt show the padding. Any idea what could be the problem?

have you tried LTRIM and RTRIM?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 2

Hmmm, wasnt aware of a LTRIM and RTRIM function in SQL..whats the syntax for that?

cust_id is of type char also

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
Hmmm, wasnt aware of a LTRIM and RTRIM function in SQL..whats the syntax for that?

LTRIM(expression)

quote:
cust_id is of type char also

fixed-length values anyway?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

What I meant with fixed-length, is you normally use char data type, when you expect the stored values to be of the same length. When they vary in size you should use varchar

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 5

I see...........well the reason for using char is that Im on the way to using Business Objects and it doesnt seem to like varchar. Ill give the TRIM functions a go and get back to you.....

Cheers Frank

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

achowe SSCrazy Points: 2075 More actions · Answer 6

Ok..tried LTRIM and RTRIM but no joy.still the same result

I haev a hunch the problem doesnt lie withinthe size of the fields themselves..what do you think?

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

beath SSCrazy Points: 2939 More actions · Answer 7

Instead of using LEN, See what values you get back from using

DATALENGTH(expression)

The LEN function ignores any trailing spaces - DATALEGTH will return the true length.

Should rule out field sizes on way or another....

achowe SSCrazy Points: 2075 More actions · Answer 8

Have tried this......DATALENGTH only return the length that I have set when formatting the fields which can of course be viewed in design view............

Thanks for the idea though!

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
Have tried this......DATALENGTH only return the length that I have set when formatting the fields which can of course be viewed in design view............
Thanks for the idea though!

I don't know Business Objects, maybe this one will illustrate what beath meant

DECLARE @a char(10)

DECLARE @b-2 char(10)

DECLARE @C char(10)

DECLARE @d char(10)

SET @a = '1'

SET @b-2 = '2'

SET @C = '3'

SET @d = '4'

SELECT DATALENGTH(@a+@b+@c+@d)

Returns 40, right, although only four characters assigned

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 10

I can see what the function does by your example, but as far as I can see it returns an obvious answer, I dont think it will solve my problem......

your help really is appreciated, cheers for that guys

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

quote:
I can see what the function does by your example, but as far as I can see it returns an obvious answer, I dont think it will solve my problem......

so I guess you have to post your code

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 12

See first post! Thats all there is too it, the problem must lie in there somewhere.....

update clnpers set cust_id = shop_id + rec_sname + rec_fname + rec_bday;

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

skrilla99 SSCertifiable Points: 5795 More actions · Answer 13

Andrew,

How about simply adding a cast to varchar around each field being concatenated? Something like this?

update clnpers

set cust_id = cast(shop_id as varchar) + cast(rec_sname as varchar) + ...etc

Dan B

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

and what does DATALENGTH show you?

Is cust_id large enough to take all the data?

What about converting to varchar and then updating?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]