SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Concat asterisk equal length		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
180 Posts
Posted - 01/23/2013 :  08:19:52  Show Profile  Reply with Quote
Hello,
I need to place an asterisk at the same column in this simple resultset:

description
City gr. 1 *
City gr. 4 *
City gr. 1 - Blocked *
City gr. 2 - Blocked *

to obtain:


description
City gr. 1 *
City gr. 4 *
City gr. 1 - Blocked *
City gr. 2 - Blocked *

How can I accomplish this?

Luigi

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1795 Posts
Posted - 01/23/2013 :  08:22:33  Show Profile  Visit jackv's Homepage  Reply with Quote
These two look the same . Is the first ome supposed to be multiple columns?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
180 Posts
Posted - 01/23/2013 :  08:26:30  Show Profile  Reply with Quote
Sorry, in this way: 

description
City gr. 1           *
City gr. 4           *
City gr. 1 - Blocked *
City gr. 2 - Blocked *


PS
I have to stay with SQL Server 2008 R2 features.
Edited by - Ciupaz on 01/23/2013 08:29:08
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1711 Posts
Posted - 01/23/2013 :  08:29:45  Show Profile  Reply with Quote
See this one......

DECLARE @t TABLE(col varchar(100))
insert into @t
select 'City gr. 1 *' union all
SELECT 'City gr. 4 *' union all
SELECT 'City gr. 1 - Blocked *' union all
SELECT 'City gr. 2 - Blocked *'

;with cte as(SELECT MAX(len(col)) largLen FROM @t)
SELECT Left(COL, len(col)-1) + REPLICATE(' ', largLen-LEN(col))+ '*' FROM cte, @t
Edited by - bandi on 01/23/2013 08:40:46
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
180 Posts
Posted - 01/23/2013 :  08:32:06  Show Profile  Reply with Quote
Not so simply. The asterisk have to stay in the same column of the result, independently of Description field lenght.

Luigi
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/23/2013 :  13:32:24  Show Profile  Reply with Quote
I think the code bandi posted does just that - it may appear to be not so because the result window probably is using a proportional font.

If you know how far to the right you want the asterisk to be, another way would be to cast it to char or nchar of that length and then append the star. For example:
SELECT CAST(LEFT(col,LEN(col)-1) AS CHAR(40))+'*'
FROM @t
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
180 Posts
Posted - 01/24/2013 :  03:35:17  Show Profile  Reply with Quote
Thank you James, this works correctly.

Luigi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000