Reformatting Field results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reformatting Field results

Author

Topic

abuhassan

105 Posts

Posted - 2006-07-24 : 07:28:52

Hi

I am trying to make a query from a sql server 2000 database i have a table as follows:

Operatinngsystem FQDomain
============= ========
Windows server1.gnet.net
Windows server2.gnet.net
Windows server3.gnet.net

what i want to be able to do is to return the result so that the server1-3 asre in a seperate colunm and the gnet.net seperate i want the out put as follows:

Operatinngsystem Server Domain
============= ====== ======
Windows server1 gnet.net
Windows server2 gnet.net
Windows server3 gnet.net

therefore three colunms output. I would be greatful if any one got any ideas how i can do this? (Please note im new to sql server)

Thanks

abuhassan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 07:34:54

[code]select OperatingSystem,
left(FQDomain, charindex('.', FQDomain) - 1) as Server,
right(FQDomain, len(FQDomain) - charindex('.', FQDomain) - 1) as Domain
from
(
select 'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain union all
select 'Windows', 'server2.gnet.net' union all
select 'Windows', 'server3.gnet.net'
) t[/code]

abuhassan

105 Posts

Posted - 2006-07-24 : 08:13:09

Hi

I get an error when i try to execute the query on my table

Operatinngsystem FQDomain
============= ========
Windows server1.gnet.net
Windows server2.gnet.net
Windows server3.gnet.net
Windows server4.gnet.net
Windows server5.gnet.net
Windows server6.gnet.net
Windows server7.gnet.net
Windows Unknown
Unix tree.tree.net
Unix sec.tree.net
Unix Unknown
Unix Unknown
Unix Unknown
Unix Unknown
Unix Unknown

Could it be because of the the unkown in some of the fields?

is there a way round it?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 08:22:18

How about this ..??


select	OperatingSystem, 
                PARSENAME(FQDomain,3) as Server, 
                 PARSENAME(FQDomain,2)+'.'+PARSENAME(FQDomain,1) as Domain
from
(
	select	'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain	union all
	select	'Windows', 'server2.gnet.net'	union all
	select	'Windows', 'server3.gnet.net'
) t


EDIT:
select	OperatingSystem, 
                PARSENAME(FQDomain,3) as Server, 
                 PARSENAME(FQDomain,2)+'.'+PARSENAME(FQDomain,1) as Domain
from
(
	select	'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain	union all
	select	'Windows', 'server2.gnet.net'	union all
	select	'Windows', 'server3.gnet.net' Union All
	Select  'Unix',  'tree.tree.net' Union All 
	Select 'Unix', 'sec.tree.net' Union All
	Select 'Unix', 'Unknown' Union All
	Select 'Unix', 'Unknown' 
) t

Where there is no Unknow it shows null, if you want to display somthing
elsee then you can make you of isnull function

Chirag

abuhassan

105 Posts

Posted - 2006-07-24 : 08:36:25

hi

im still getting error in the results

Operatinngsystem FQDomain
============= ========
Windows server1.gnet.net
Windows server2.gnet.net
Windows server3.gnet.net
Windows server4.gnet.net
Windows server5.gnet.net
Windows server6.gnet.net
Windows server7.gnet.net
Windows Unknown
Unix tree.tree.net
Unix sec.tree.net
Unix Unknown
Unix Unknown
Unix Unknown
Unix Unknown
Unix Unknown

soem of the the FQdomain are as follows: ukserver1.ser.co.uk, ukserver2.wa.ser.co.uk etc...

could that be the cause of the problem

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 08:39:29

What error message you are getting ?

Chirag

abuhassan

105 Posts

Posted - 2006-07-24 : 08:45:01

Where i have got the FQdomain as follows: ukserver1.ser.co.uk, ukserver2.wa.ser.co.uk etc

it returns a null in the server and the domain colunms..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 08:59:22

try this

select	OperatingSystem, Server, case when FQDomain <> Server then right(FQDomain, len(FQDomain) - len(Server)) else '' end as Domain
from
(
	select	OperatingSystem, FQDomain, reverse(parsename(reverse(FQDomain), 1)) as Server
	from
	(
		select	'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain	union all
		select	'Windows', 'server2.gnet.net'	union all
		select	'Windows', 'server3.gnet.net'	union all
		select	'Windows', 'server4.gnet.net'	union all
		select	'Windows', 'server5.gnet.net'	union all
		select	'Windows', 'server6.gnet.net'	union all
		select	'Windows', 'server7.gnet.net'	union all
		select	'Windows', 'Unknown'	union all
		select	'Unix', 'tree.tree.net'	union all
		select	'Unix', 'sec.tree.net'	union all
		select	'Unix', 'Unknown'	union all
		select	'Unix', 'Unknown'	union all
		select	'Unix', 'Unknown'	union all
		select	'Unix', 'Unknown'	union all
		select	'Unix', 'Unknown'
	) t
) a

abuhassan

105 Posts

Posted - 2006-07-24 : 09:19:33

Hi

I still get the same error of null field where the FQdomain is like : ukserver1.ser.co.uk, ukserver2.wa.ser.co.uk etc?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 09:28:50

Try this


select	OperatingSystem,CharIndex('.',FQDomain),
		Case When (CharIndex('.',FQDomain)>0) Then left(FQDomain, charindex('.', FQDomain) - 1) Else FQDomain End 
		 as Server, 
		Case When (CharIndex('.',FQDomain)>0) Then  right(FQDomain, len(FQDomain) - charindex('.', FQDomain))  Else FQDomain End 
		as Domain
from
(
	select	'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain	union all
	select	'Windows', 'server2.gnet.net'	union all
	select	'Windows', 'server3.gnet.net' Union All
	Select  'Unix',  'tree.tree.net' Union All 
	Select 'Unix', 'sec.tree.net' Union All
	Select 'Unix', 'Unknown' Union All
	Select 'Unix', 'Unknown' Union All 
	Select 'Unix', 'ukserver1.ser.co.uk'
) t

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 09:30:24

[code]select OperatingSystem, Server, case when FQDomain <> Server then right(FQDomain, len(FQDomain) - len(Server) - 1) else '' end as Domain
from
(
select OperatingSystem, FQDomain, reverse(parsename(reverse(FQDomain), 1)) as Server
from
(
select 'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain union all
select 'Windows', 'server2.gnet.net' union all
select 'Windows', 'Unknown' union all
select 'Unix', 'tree.tree.net' union all
select 'Unix', 'sec.tree.net' union all
select 'Unix', 'Unknown' union all
select 'Unix', 'ukserver1.ser.co.uk'
) t
) a

/* RESULT
OperatingSystem Server Domain
--------------- --------------- -------------------
Windows server1 gnet.net
Windows server2 gnet.net
Windows Unknown
Unix tree tree.net
Unix sec tree.net
Unix Unknown
Unix ukserver1 ser.co.uk
*/[/code]

abuhassan

105 Posts

Posted - 2006-07-24 : 09:37:17

Hi

I still get the same error of null field where the FQdomain is like : ukserver2.wa.ser.co.uk ukserver3.wa.ser.co.uk ukserver4.wa.ser.co.uk etc?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 09:48:57

[code]
select OperatingSystem,
Case When (CharIndex('.',FQDomain)>0) Then left(FQDomain, charindex('.', FQDomain) - 1) Else FQDomain End
as Server,
Case When (CharIndex('.',FQDomain)>0) Then right(FQDomain, len(FQDomain) - charindex('.', FQDomain)) Else FQDomain End
as Domain
from
(
select 'Windows' as OperatingSystem, 'server1.gnet.net' as FQDomain union all
select 'Windows', 'server2.gnet.net' union all
select 'Windows', 'server3.gnet.net' Union All
Select 'Unix', 'tree.tree.net' Union All
Select 'Unix', 'sec.tree.net' Union All
Select 'Unix', 'Unknown' Union All
Select 'Unix', 'Unknown' Union All
Select 'Unix', 'ukserver1.ser.co.uk' Union all
Select 'Unix', 'ukserver2.wa.ser.co.uk'
) t

Output

Windows server1 gnet.net
Windows server2 gnet.net
Windows server3 gnet.net
Unix tree tree.net
Unix sec tree.net
Unix Unknown Unknown
Unix Unknown Unknown
Unix ukserver1 ser.co.uk
Unix ukserver2 wa.ser.co.uk

[/code]

Chirag

abuhassan

105 Posts

Posted - 2006-07-24 : 10:44:05

Hi

Thanks alot that works........ perfect...

ill be greatfl if you could please let me know where i can learn more about the case statement and str functions ....

please note ive little experience of SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-24 : 11:01:45

If you use front end application, then you can use split function there

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-25 : 01:46:06

quote:

if you could please let me know where i can learn more about the case statement and str functions

Book online is the best help for learning SQL Server, just press f1 from the query
Analyser and type the stuff which you required and give you get sufficeint answers.

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources