Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Equals space between two string when concatenate two columns


Equals space between two string when concatenate two columns

Author
Message
GuruGPrasad
GuruGPrasad
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 583
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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.

Cool
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
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)
GuruGPrasad
GuruGPrasad
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 583
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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?

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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.

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
FYI, I posted the output I generated into Notepad and it looks good, the currency codes are all lined up nicely.

Cool
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)
GuruGPrasad
GuruGPrasad
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 583
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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


Cool
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search