CODEWITHSUNDEEP
sql-serverdistinct
Matt
Matt

Reputation: 5788

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?

Upvotes: 0

Views: 40

Answers (2)

Olivier De Meulder
Olivier De Meulder

Reputation: 2501

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))
     );

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

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

Upvotes: 2

Related Questions