Concatenation

Question

Post reply

Concatenation

Viewing 10 posts - 16 through 24 (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 Dan,

funny things happen, when you think too long on an answer

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

Yeah, cust_id is large enough without a doubt. the casting doesnt seem to hav an effect

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

Maybe this thread can help

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=12594

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 4

Actually, one point which may be of interest......previous to today this wasnt a problem, the cust_id field appeared as one long concatanated string with no spaces. the code hasnt been changed that Im aware of, so Im guessing an external factor may have caused the problem. Does anyone know of anything that could possible effect this?

Cheers geezers!

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 5

Andrew,

So that we may more accurately reproduce your problem can you provide sample code from beginning to end that fails execution, including sample data?

Dan B

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 6

I think you are trimming after concatatenation only

Try

update clnpers set cust_id = ltrim(rtrim(shop_id)) + ltrim(rtrim(rec_sname)) + ltrim(rtrim(rec_fname)) + ltrim(rtrim(rec_bday))

example:

if shop_id ='1 '

rec_sname='ss '

rec_fname='ff '

rec_bday ='3/1/2003 '

you end result will be '1ssff3/1/2003' + additional spaces

Since cust_id is char the remaining space will be filled with space.

G.R. Preethiviraj Kulasingham

Chief Technology Officer.

Softlogic Information Systems Limited,

14 De Fonseka Place,

Colombo 05.

Sri Lanka.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

BOL 2000: The char data type is a fixed-length data type when the NOT NULL clause is specified. If a value shorter than the length of the column is inserted into a char NOT NULL column, the value is right-padded with blanks to the size of the column. For example, if a column is defined as char(10) and the data to be stored is "music", SQL Server stores this data as "music_____", where "_" indicates a blank.

Seems Preethi maybe on to something.

If all the relevant columns are char(?) then ltrim(trim()) will remove the spaces during concatenation but when assigning the value to CUST_ID it will be padded again.

achowe SSCrazy Points: 2075 More actions · Answer 8

Preethi's suggestion worked, cheers Preethi

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

Hi Andrew,

quote:
Preethi's suggestion worked, cheers Preethi

didn't you say the LTRIM, RTRIM doesn't work for you?

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

As preethi assumed, I was trimming after concatenation, therefore it wasnt working. Thanks for the help 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!