Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Equals space between two string when concatenate two columns Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 3:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:30 PM
Points: 77, Visits: 574
Table Script

CREATE TABLE [dbo].[currency](
[currency_code] [varchar](3) NOT NULL,
[country] [varchar](256) NOT NULL,
[currency_name] [varchar](256) NULL,
[active] [int] NULL,
CONSTRAINT [PK_currency] PRIMARY KEY CLUSTERED
(
[currency_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Insert Script

INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AED' ,'United Arab Emirates',NULL,0)

INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AFN' ,'AFN',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ALL' ,'ALL',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AMD' ,'Armenia, Drams',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ANG' ,'Netherlands Antilles, Guilders (also called Florins)',NULL,0)


I need to concatenate country and currency_code with equal space


My desired output is
Combined string
------------------------------------------------------------
United Arab Emirates, Dirhams AED
Afghanistan, Afghanis AFN
Albania, Leke ALL
Armenia, Drams AMD
Netherlands Antilles, Guilders (also called Florins) ANG

Space between the two string should depends on max(len(country))

Any one help me. Thanks in advance..

Regards,
Guru
Post #1354208
Posted Tuesday, September 4, 2012 3:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 23,041, Visits: 31,564
GuruGPrasad (9/4/2012)
Table Script

CREATE TABLE [dbo].[currency](
[currency_code] [varchar](3) NOT NULL,
[country] [varchar](256) NOT NULL,
[currency_name] [varchar](256) NULL,
[active] [int] NULL,
CONSTRAINT [PK_currency] PRIMARY KEY CLUSTERED
(
[currency_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Insert Script

INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AED' ,'United Arab Emirates',NULL,0)

INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AFN' ,'AFN',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ALL' ,'ALL',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AMD' ,'Armenia, Drams',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ANG' ,'Netherlands Antilles, Guilders (also called Florins)',NULL,0)


I need to concatenate country and currency_code with equal space


My desired output is
Combined string
------------------------------------------------------------
United Arab Emirates, Dirhams AED
Afghanistan, Afghanis AFN
Albania, Leke ALL
Armenia, Drams AMD
Netherlands Antilles, Guilders (also called Florins) ANG

Space between the two string should depends on max(len(country))

Any one help me. Thanks in advance..

Regards,
Guru


I'm sorry, but I am lost on what you are asking for here.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354213
Posted Tuesday, September 4, 2012 3:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,110, Visits: 11,943
Nice job posting ddl and sample data. I don't understand what you want for output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1354214
Posted Tuesday, September 4, 2012 3:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:30 PM
Points: 77, Visits: 574
I need to display country and currency_code in a single column.

select country + space(46-Len(country)) + CONVERT(varchar(max), currency_code ) from currency order by country

Afghanistan, Afghanis -AFN
Albania, Leke -ALL
Algeria, Algeria Dinars -DZD
Angola, Kwanza -AOA
Argentina, Pesos -ARS
Armenia, Drams -AMD

Regards
Guru
Post #1354223
Posted Tuesday, September 4, 2012 3:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 23,041, Visits: 31,564
GuruGPrasad (9/4/2012)
I need to display country and currency_code in a single column.

select country + space(46-Len(country)) + CONVERT(varchar(max), currency_code ) from currency order by country

Afghanistan, Afghanis -AFN
Albania, Leke -ALL
Algeria, Algeria Dinars -DZD
Angola, Kwanza -AOA
Argentina, Pesos -ARS
Armenia, Drams -AMD

Regards
Guru


You do know that the longest country in your table is 53 characters long, right?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354225
Posted Tuesday, September 4, 2012 3:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 23,041, Visits: 31,564
Two things. One, your code would simply look like this:

select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;


Two, be sure to use a fixed space font, not a proportional font, to display the output.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354228
Posted Tuesday, September 4, 2012 3:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 23,041, Visits: 31,564
FYI, I posted the output I generated into Notepad and it looks good, the currency codes are all lined up nicely.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354229
Posted Tuesday, September 4, 2012 3:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:30 PM
Points: 77, Visits: 574
Thanks Lynn Pettis

Max column length may vary.

And your code

select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
does not suit my needs.

Please find the attachment

Regards
Guru
Post #1354232
Posted Tuesday, September 4, 2012 4:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 23,041, Visits: 31,564
GuruGPrasad (9/4/2012)
Thanks Lynn Pettis

Max column length may vary.

And your code

select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
does not suit my needs.

Please find the attachment

Regards
Guru


What attachment, and I just followed what you posted:

GuruGPrasad (9/4/2012)
I need to display country and currency_code in a single column.

select country + space(46-Len(country)) + CONVERT(varchar(max), currency_code ) from currency order by country

Afghanistan, Afghanis -AFN
Albania, Leke -ALL
Algeria, Algeria Dinars -DZD
Angola, Kwanza -AOA
Argentina, Pesos -ARS
Armenia, Drams -AMD

Regards
Guru





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354236
Posted Wednesday, September 5, 2012 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,110, Visits: 11,943
GuruGPrasad (9/4/2012)
Thanks Lynn Pettis

Max column length may vary.

And your code

select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
does not suit my needs.

Please find the attachment

Regards
Guru


This screams of a report to me. This type of formatting should be done in the front end. It is far easier in the front end than using t-sql to format data. If you have to use t-sql for this you will need to use dynamic sql.

Something like this:

declare @MaxLen int

select @MaxLen = MAX(len(country)) + 2 from currency

declare @sql varchar(max)

select @sql = 'select CAST(country as CHAR(' + cast(@MaxLen as varchar(3)) + ')) + currency_code from currency order by country'

exec (@sql)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1354525
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse