如何在 TSQL 中将 varchar 字段向右对齐? [英] How can I align a varchar field to the right in TSQL?

问题描述

我在名为11(space)"、109"、X(space)(space)"、9(space)(space)"、15(space)"等的列中有值我的代码是 varchar(3).

I have values like " 11(space)", "109", "X(space)(space)", "9(space)(space)", "15(space)" etc. in a column called MyCode that is varchar(3).

当我订购它们时，当我以 ASC 顺序订购它们时，我有一个像 11 这样的问题出现在 109 之前.

When I order them I have a prolbme like 11 appears before 109 when I ORDER them in ASC order.

我想通过在前面添加空字符来使值向右对齐，以便 ORDER 会导致:

I want to align the values to right by adding empty char in front so that ORDER would result:

 " 11"

 .. 

"109"

问题:如何编写 SELECT MyChar FROM MyTable 以便对于值1(space)(space)"它应该返回(space)(space)1"等?

Question: How can I write my SELECT MyChar FROM MyTable so that for a value "1(space)(space)" it should return "(space)(space)1" etc. ?

@gbn:您的回答满足了我的需要，但输出没有改变:

@gbn: Your answer did what I needed but the output didnt change:

推荐答案

使用它总是将前导空格填充到固定长度

Use this to always pad with leading spaces to a fixed length

SELECT RIGHT(
      SPACE(5) + 
      REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(MyChar), CHAR(160), ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')
    , 5) AS Justified
FROM MyTable 
ORDER BY Justified

如果你改变一个5，你需要同时改变两个

If you change one 5, you need to change both

添加 RTRIM

Edit 2: 添加硬空间 + 其他不可打印检测

Edit 2: added hard space + other non-printable detection

这给你带来了什么?

SELECT ASCII(RIGHT(MyChar, 1) FROM MyTable

这篇关于如何在 TSQL 中将 varchar 字段向右对齐?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！