How do i right justify data in a nchar or nvarchar field? e.g.
now:
2bbb
3bbb
desired :
bbb2
bbb3
where bbb is either blank or null, not certain.
I would appreciate any help
Pauley
Hi,see if you come around with that:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @.intCount int
SET @.intCount = 0
WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars_drkw(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||hi Pauley,
actually I'd not, as this only seems to me a "presentation/redering" problem that should be managed at the presentation level, thus your app...
I'm asking: are you really sure you want to "pad" data on the right? this could require handling query statements differently if your user just entries "2" instead of " 2" ...
regards
|||Andrea, thanks for responding. Below shows the situation better. The field is a nchar field sorted in ascending sequence using Visual Studio express 's Database Manager. I think nvarchar fields does the same. As u can see the the third field (1013 ) and the 14th field (1014) should be the first 2 in the sorted records. Pure alpha fields like Names, City Names etc are ok but pure numbers come out like this. Is this because the nchar and nvarchar field are intended to be alphabetic ? So how can i get a proper sequence , Use an integer or numeric type field ?
Pauley
10128
Jens, thanks for responding. Below shows the situation better. The field is a nchar field sorted in ascending sequence using Visual Studio express 's Database Manager. I think nvarchar fields does the same. As u can see the the third field (1013 ) and the 14th field (1014) should be the first 2 in the sorted records. Pure alpha fields like Names, City Names etc are ok but pure numbers come out like this. Is this because the nchar and nvarchar field are intended to be alphabetic ? So how can i get a proper sequence , Use an integer or numeric type field ?
Pauley
10128
hi Pauley,
in your case it seems to me your choice for an nchar datatype is not correct as your attribute's values are all numeric in the integer domain.. the integer datatype should be the best choice...
but you could argue that, in the future, your attribute could also be set with a mix of alpha + numeric values, like a trailing letter and some digits: "A110", "C99" and the result should be ordered according to alpha + numeric rules so that A110 sorts before B1..
in this cases you can play a little with the ORDER BY clause, casting or alligning part of the attribute's value like the 3rd sample enclosed..
USE tempdb;
GO
CREATE TABLE #test (
Id nchar(5) NOT NULL PRIMARY KEY
);
GO
INSERT INTO #test VALUES ( '10' );
INSERT INTO #test VALUES ( '1' );
INSERT INTO #test VALUES ( '100' );
INSERT INTO #test VALUES ( '11' );
INSERT INTO #test VALUES ( '110' );
INSERT INTO #test VALUES ( '2' );
INSERT INTO #test VALUES ( '9' );
INSERT INTO #test VALUES ( '22' );
INSERT INTO #test VALUES ( '99' );
GO
PRINT 'Default sort for alphabetic columns';
SELECT * FROM #test ORDER BY Id;
GO
PRINT 'simple solution casting to integer';
SELECT * FROM #test ORDER BY CONVERT(int,Id);
GO
PRINT 'rigth aligning so that " 10" sorts after " 2"';
SELECT *
FROM #test ORDER BY SPACE((DATALENGTH(Id)/2 - LEN(Id))) + Id;
GO
TRUNCATE TABLE #test;
GO
INSERT INTO #test VALUES ( 'A10' );
INSERT INTO #test VALUES ( 'B1' );
INSERT INTO #test VALUES ( 'C100' );
INSERT INTO #test VALUES ( 'A11' );
INSERT INTO #test VALUES ( 'A110' );
INSERT INTO #test VALUES ( 'A2' );
INSERT INTO #test VALUES ( 'A9' );
INSERT INTO #test VALUES ( 'B22' );
INSERT INTO #test VALUES ( 'B99' );
GO
PRINT 'trailing Alpha + numeric';
SELECT Id AS [Id sorted by current collation]
FROM #test ORDER BY LEFT(Id,1), SPACE(((DATALENGTH(Id)/2 -1) - (LEN(Id)-1))) + RIGHT(Id, LEN(Id)-1);
PRINT 'specifying a specific collation';
PRINT 'for the particular/required sort rules';
SELECT Id AS [Id sorted by Latin1_General_BIN]
FROM #test ORDER BY LEFT(Id,1) + SPACE(((DATALENGTH(Id)/2 -1) - (LEN(Id)-1))) + RIGHT(Id, LEN(Id)-1) COLLATE Latin1_General_BIN;
GO
DROP TABLE #test;
--<--
Default sort for alphabetic columns
Id
--
1
10
100
11
110
2
22
9
99
simple solution casting to integer
Id
--
1
2
9
10
11
22
99
100
110
rigth aligning so that " 10" sorts after " 2"
Id
--
1
2
9
10
11
22
99
100
110
trailing Alpha + numeric
Id sorted by current collation
A2
A9
A10
A11
A110
B1
B22
B99
C100
specifying a specific collation
for the particular/required sort rules
Id sorted by Latin1_General_BIN
-
A2
A9
A10
A11
A110
B1
B22
B99
C100
obviously, you should be aware that when "sorting" values you have to consider the collation you are dealing with, as sort rules can be quite different among different collations.. my results depends (except the last one where a specific sort rule has been set via an explicit COLLATE [collation_name] clause) on my collation settings,
SELECT SERVERPROPERTY('Collation') AS [My default collation];--<-
My default collation
Latin1_General_CI_AS
regards
|||Ok, you don′t need numeric fields as you don′t wan to have decimal values within your table in this column, right ? So you better use integer or any smaller integer data type. If you just want to use it for adhoc reporting and you cannot change the data type due to the consuming application, you could either use a cast within your order ORDER BY CAST(Thecolumn as int) or use another column to maintain the data, as the on-the-fly approach would not be very performant on heavy queries.HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Andrea, thank u very much. That resolved my question.
Pauley
Jens, thank u very much. I will use the data type properly.
pauley
pjmcb wrote:
Forget the Settings BUDDY