concat two strings and put smaller string at first in sql server-CodePudding

for concating two varchars from columns A and B ,like "1923X" and "11459" with the hashtag, while I always want the smallest string become at first, what should I do in SQL server query?

inputs: Two Columns

A="1923X"

B="11459"

procedure:

while we are checking two inputs from right to left, in this example the second character value in B (1) is smaller than the second character in A (9) so B is smaller.

result: new column C

"11459#1923X"

CodePudding user response：

Original answer:

If you need to order the input strings, not only by second character, STRING_AGG() is also an option:

DECLARE @a varchar(5) = '1923X'
DECLARE @b varchar(5) = '11459'

SELECT STRING_AGG(v.String, '#') WITHIN GROUP (ORDER BY v.String) AS Result
FROM (VALUES (@a), (@b)) v (String)

Output:

Result
11459#1923X

Update:

You changed the requirements (now the strings are stored in two columns), so you need a different statement:

SELECT 
   A, 
   B, 
   C = (
      SELECT STRING_AGG(v.String, '#') WITHIN GROUP (ORDER BY v.String)
      FROM (VALUES (A), (B)) v (String)
   )
FROM (VALUES ('1923X', '11459')) t (a, b)