Appending numbers and text

Question

Appending numbers and text

Franco_1

Say Hey Kid

Points: 663
More actions
October 3, 2008 at 9:04 am

#213530

I have to concatenate fields from a table into on huge column in another table. Problem is some of the data from some of the columns are varchar, float and decimals. the original data was imported from Access.
How do I concatenate these fields into one column. Also, if the column is null or empty, I have to insert ' ' as a delimiter.
Sample:
col1,col2,col3,col4 (assuming no data in col3 and col4 is null)
I have to put them together to look like this - note there are 2 delimiters at the end for empty data and a null field:

Viewing 11 posts - 1 through 10 (of 10 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

The only way to combine all of those types is to get them into a varchar datatype.

The real question is why do you need to combine the columns? I'm going to assume this is for some sort of export to another application, and the file spec is being dictated to you. If so, fine do what you have to do. If this is instead for some sort of database to be used by your company, why combine them? What's the business reason? You should be able to combine them on output to a front end, don't store it that way.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Guess I forgot to answer the original question...

SELECT cast(col1 as varchar(10)) + CAST (Col2 as varchar())

For the nulls you could use cast(isnull(col1,'') as varchar())

To help us help you read this[/url]For better help with performance problems please read this[/url]

Franco_1 Say Hey Kid Points: 663 More actions · Answer 3

You are very right, Luke.

The file is for a client and they

dictate how they want it.

Concatenating numbers and text and delaing with nulls

is crazy.

Franco_1 Say Hey Kid Points: 663 More actions · Answer 4

When I use CAST it turns a 10 digit phone

number into this - 5.19753e+009.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Your other option would be to look into using SSIS...

You could specify a text file datatype and use SSIS to output and transform it however you need. it's very flexible...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

For a 10 digit phone number, I'm guessing it's already a character datatype and not an int so there would be no need to cast it. You can append a character datatype to another one without another explicit cast...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 7

The phone number is a float, and when using CAST, it will give you scientific notation.

Try this one:

STR(phone, 10,0)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 8

yeah it's the precision that's screwing you.

try this...

declare @phone float

SELECT @phone = 5197531212

select cast(cast( @phone as decimal) as char(10))

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 9

I'm actually surprised that the data being in a variety of less than optimal datatypes, like a float for a phone number, isn't causing you issues all over your app. I'd seriously consider using this as a way to talk management into giving you the time and resources to correct it...

Additionally I'd really look at using SSIS or DTS to extract this data as it will be tons easier than having to cast/convert everything into one big long string...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Franco_1 (10/3/2008)
I have to concatenate fields from a table into on huge column in another table. Problem is some of the data from some of the columns are varchar, float and decimals. the original data was imported from Access.
How do I concatenate these fields into one column. Also, if the column is null or empty, I have to insert ' ' as a delimiter.
Sample:
col1,col2,col3,col4 (assuming no data in col3 and col4 is null)
I have to put them together to look like this - note there are 2 delimiters at the end for empty data and a null field:

Despite it's title, there's a pretty good article on how to do such a thing located at the URL in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)