SQL SELECT query output with specific format

Question

I need, if possible, a T-SQL query that will return the values in a specific format from the columns.

I can do this programmatically (c#), but is it possible to do in query using SQL Server 2008 ?

Here's the current query

select id, server_name, domain_name 
from mastertable

Current output:

id server_name   domanin_name
1  sys1          one.abc.in
2  sys2          two.pqr.in 
3  sys3          three.abc.in 
4  sys4          four.xyz.in

I need to display data as (skip value after (.) for column domain_name):

id server_name domanin_name
1  sys1        one
2  sys2        two
3  sys3        three 
4  sys4        four

Thanks in advance

Miika L. · Accepted Answer · 2012-04-30 09:57:12Z

2

You could try something like:

SELECT id, server_name, SUBSTRING(domain_name, 1, CHARINDEX('.', domain_name) - 1)
FROM mastertable

answered Apr 30, 2012 at 9:57

Miika L.

3,3491 gold badge27 silver badges36 bronze badges

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

6 Comments

marc_s Over a year ago

Works - as long as you're sure that domain_name will always contain a . - if that's not the case, your query will fall flat on its nose (Msg 537, Level 16, State 2, Line 20 Invalid length parameter passed to the LEFT or SUBSTRING function.) ...

Miika L. Over a year ago

@marc_s But if the field is "domain name", then is it a valid domain name in the first place if it doesn't contain a "."? But in any case, you make a valid point.

marc_s Over a year ago

Yes - I would assume, too, that such a column would include a . - but I'd rather check to make sure, rather than just assume ... (principle of defensive programming)

Satinder singh Over a year ago

SELECT id, server_name, SUBSTRING(domain_name, 1, CHARINDEX('.', domain_name) - 1) FROM mastertable this give one., two. DID LIL EDIT SELECT id, server_name, SUBSTRING(domain_name, 0, CHARINDEX('.', domain_name) - 1) FROM mastertable

Miika L. Over a year ago

Curious, because starting from zero doesn't work for me (end up with "on", "tw", etc. Are you sure you had the -1 after the char index?

|

marc_s · Accepted Answer · 2012-04-30 09:59:02Z

1

You could use something like this:

SELECT
    id,
    server_name,
    mod_domain_name =
        CASE CHARINDEX('.', domain_name)
            WHEN 0 THEN domain_name
            ELSE SUBSTRING(domain_name, 1, CHARINDEX('.', domain_name) - 1)
        END
FROM mastertable

This tests to make sure there's a . in the domain_name, and if so, it "clips" the domain name to show only the bits before the first .

answered Apr 30, 2012 at 9:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

SQL SELECT query output with specific format

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related