Combining established columns into one

I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?Could you please post your syntax?|||It is best to do the formatting for display purposes on the client. What if you want to change the formatting later? You hav e to make schema changes even if you use computed columns or views or queries in SPs.|||

you may wish to use a calculated column

use northwind
select * from employees
go
alter table employees
add
fullname as rtrim(lastname)+','+rtrim(firstname)
go

select fullname,lastname,firstname from employees

|||I realize that it would be best to do all the formatting on the client. The problem is that I have about 50 stored procedures that were developed on another database that was "supposed to" have the same table schemas. Unfortunately, the developer decided to split apart the names into first name and last name fields. It would be easier to just created a computed column.|||Actually, splitting the name into it's constituent parts and storing it is the correct way. You can use a computed column or view with the computed expression or modify your SP to include the computed expression. With all these methods, you can get the required column for display purposes. But if you want to search on this concatenated string then it is a different deal. Performance depends on lot of factors like index on the computed column, whether optimizer matches the computed column expression and uses the index and so on.

combined 2 data and separate them

I have a data grid with dropdownlist.
the dropdownlist is populated with datas wth a sql statement with 2 combined data
my sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1
When i select a value from the dropdownlist, i need to separate the data, name and id into different columns
how do i do it?
Is there a way to manipulate the sql to do such a thing?you can use the split function.

column Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.

column Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.