Ordering numbers according to length in SQL Server

Mehram

SOLVED CLOSED ACTIVE ANSWERED
User: Mehram
Posted: on Jan 20, 2023 11:23 PM
Forum: SQL Server
Answer: 1
Views: 1057

Sir

I need to display following that in GridView after adding spaces according the data length.

If the data length is 2 then add one space if four add 2 spaces, so on.

1
11
1101
1101001
1101001001
1101001001001
1101001001002
1101001001003
1101001001004
1101001001005
1101001001006
1101001001007
1101001002
1101001002001
1101001003
1101001004
1101001005
1101001006
1101001007
1101001008
1101001009
1101001010
1101001011
1101001012
1101002
1101002001
1102
1102001
1102001001
1102001002
1102001003
1102002

Select 
    AccountIDTree=Case When Len(AccountID)=2 then Char(32) + AccountID else 
    Case When Len(AccountID)=4 then Char(32)+Char(32)+ AccountID else
    Case When Len(AccountID)=7 then Char(32)+Char(32)+Char(32)+ AccountID else
    Case When Len(AccountID)=10 then Char(32)+Char(32)+Char(32)+Char(32)+ AccountID else
    Case When Len(AccountID)=13 then Char(32)+Char(32)+Char(32)+Char(32)+Char(32)+ AccountID else
    accountid end end end end end 
from abc

Please help

Download FREE API for Word, Excel and PDF in ASP.Net

PrinceG

0 2

Reply
ANSWER
User: PrinceG
Replied: on Jan 23, 2023 07:06 AM
Report

Hi Mehram,

Please refer below SQL Query.

SQL

CREATE TABLE #abc
(
	AccountID VARCHAR(100)
)

INSERT INTO #abc VALUES (1)
INSERT INTO #abc VALUES (1101)
INSERT INTO #abc VALUES (111)
INSERT INTO #abc VALUES (1101001)
INSERT INTO #abc VALUES (1101001001002)
INSERT INTO #abc VALUES (1101001001001)
INSERT INTO #abc VALUES (1101001001)

SELECT AccountIDTree = CASE 
	WHEN LEN(AccountID) = 2 THEN CHAR(32) + AccountID 
	WHEN LEN(AccountID) = 4 THEN CHAR(32) + CHAR(32) + AccountID 
	WHEN LEN(AccountID) = 7 THEN CHAR(32) + CHAR(32) + CHAR(32) + AccountID 
	WHEN LEN(AccountID) = 10 THEN CHAR(32) + CHAR(32) + CHAR(32) + CHAR(32) + AccountID 
	WHEN LEN(AccountID) = 13 THEN CHAR(32) + CHAR(32) + CHAR(32) + CHAR(32) + CHAR(32) + AccountID 
	ELSE accountid end 
FROM #abc
ORDER BY CAST(AccountID AS BIGINT)

DROP TABLE #abc

Output

AccountIDTree
1
111
1101
1101001
1101001001
1101001001001
1101001001002

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Ordering numbers according to length in SQL Server

Company

Explore

Follow us

Contact Us