Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Concat asterisk equal length

Author  Topic 

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-23 : 08:19:52
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-23 : 08:22:33
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

232 Posts

Posted - 2013-01-23 : 08:26:30
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.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-23 : 08:29:45
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
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-23 : 08:32:06
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 13:32:24
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

232 Posts

Posted - 2013-01-24 : 03:35:17
Thank you James, this works correctly.

Luigi
Go to Top of Page
   

- Advertisement -