CODEWITHSUNDEEP
sqlsql-serverdatabasems-accessvba
user1932747
user1932747

Reputation: 41

Concatenate Strings in Alphabetical Order

I am trying to concatenate strings from two separate columns. The trouble is, I need the first string in the concatenation to be the first alphabetically.

For Example:

Column 1        Column 2        Concatenation
def             abc             abcdef
abc             def             abcdef

Is there any way to do this within Access? SQL Server may also be an option, but it would be much easier for me to stay within Access.

Upvotes: 1

Views: 5182

Answers (2)

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

Sql Server version

create table a
(col1 nvarchar(100)
 ,col2 nvarchar(100)
 )
 insert into a values('abc','def')
 insert into a values('def','abgggccz')
 insert into a values('xyzabc','def')
 insert into a values('def','xyzabc')
 insert into a values('axyzabc','def')
 insert into a values('adef','acxyzabc')
 insert into a values('abcd','x')

Query

select 
case when 
LOWER(col1)>LOWER(col2)
then col2+''+col1
else
col1+''+col2
end
from a

result 

abcdef
abgggcczdef
defxyzabc
defxyzabc
axyzabcdef
acxyzabcadef
abcdx

note : if you are using sql server then you can do the same if you want to concatenate more than 2 strings

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269893

For two strings, you can stay in Access:

select iif(column1 < column2, column1 & column2, column2 & column1) as concatenation

If you had more than two strings in the row, then SQL Server would be a much better option.

Upvotes: 3

Related Questions