SqlServer Select Distinct Using Single Column

Question

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?

The ideal database would not store multiple informations in one column ;) — Tim Schmelter
– Tim Schmelter, Commented Feb 8, 2016 at 15:33
Agreed Tim... but nothing in the world I work with is ideal :) — Matt
– Matt, Commented Feb 8, 2016 at 15:37

Olivier De Meulder · Accepted Answer · 2016-02-08 15:29:46Z

2

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

answered Feb 8, 2016 at 15:29

Olivier De Meulder

2,5013 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

Excellent, thanks for your help Olivier. Of course, I had to add the table name, as well as +1 for the from arg and -1 for the to arg to print only the en-US etc.

Radu Gheorghiu · Accepted Answer · 2016-02-08 15:28:41Z

2

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

answered Feb 8, 2016 at 15:28

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Collectives™ on Stack Overflow

SqlServer Select Distinct Using Single Column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related