MattB February 2016

SqlServer Select Distinct Using Single Column

I have a column that contains the server in which the action happened as well as the country. So, for example, the column 'source' would contain 'server001[en-US]'. What I need to do is get all the distinct country codes listed.

ID  SOURCE              STATUS
==============================
1   server001[en-US]    3
2   server002[de-CH]    3
3   server005[en-US]    1
4   server001[tr-TR]    3

The ideal query would output

en-US
de-CH
tr-TR

Can anyone help with this?

Answers


Radu Gheorghiu February 2016

This should give you a list of distinct country codes:

SELECT 
   LEFT(COL, LEN(COL) - 1)
FROM (
    SELECT
        RIGHT(SOURCE, LEN(SOURCE) - CHARINDEX('[', SOURCE)) COL
    FROM TABLE) TBL


Olivier De Meulder February 2016

You would need to play with substring and charindex. charindex will give you the location of a string within a column and substring is pretty obvious.

I did not test this, but something like this should do the trick:

select distinct substring(source
     , charindex('[', source)
     , charindex(']', source) - charindex('[', source))
     );

Post Status

Asked in February 2016
Viewed 3,338 times
Voted 14
Answered 2 times

Search




Leave an answer