Transact SQL and the Null Character

Background

Transact SQL is a very high level language and 99.99% of the time one does not really have to pay attention.

This morning for me was not one of those times that I did not have to be attentive.

Lineage

A quick following up to our last post :-

SQL Server – Transact SQL – Get Network Info
Link

In that post we discussed a couple of options for discovering network metadata per SQL Server Instances.

Query

Original Query

Here is our original query

SQL


select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

Output

sysDOTdm_server_registry__20180607_0918PM

Revised Query – Filter Out Null and Empty Records

In our revised query we will filter out null and empty records

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

Output

sysDOTdm_server_registry__20180607_0918PM

Explanation

Our filter clauses “is Not null” and != ” did not help.

Revised Query – Filter Out Null Character

In our second attempt, we filter out records that simply have the NULL CHARACTER as there whole value

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

and tblDSR.value_data != char(0)

Output

sysDOTdm_server_registry__20180607_0925AM

Explanation

Our filter clause is != char(0) and did discard the unwanted records.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s