T-sql 2012 display spaces

Transact-SQL
1

I have setup a test view in t-sql 2012 which is as follows:

CREATE VIEW [dbo].[vDirectCer]
AS
select top 100 percent *
from test.dbo.DirectCer
order by mailAddress desc,city desc ,state desc ,zip desc
GO

However the sql I listed does not work as I needed it to.

What I need if any or all of the fields called mailAddress,city,state ,zip
contain null or spaces, the all 4 columns in the view need to display spaces.

In addition when these fields display spaces in the view, those records should be displayed first in the list of records that are displayed.
Thus would you show me the -t-sql 2012 that can be used to accomplish this goal?

2

I'm a little unclear on your requirements. If any of the four columns is null or contains spaces, each of the four columns should return a single space? "Contains": As in "New York", which contains a space, should render all columns as space? "Returns spaces": Would an empty string ("") be what you want? I don't mean to appear so dense but perhaps some sample data and expected outputs would clarify...
The second issue is how to order the result set so that the empty records come up first. This should work, assuming that the null/space issue is resolved in the CTE:;with cteData as ( << handle the null/space issue for the four fields and using empty string >> ) select top 100 percent * from cteData order by case when zip = '' then 1 -- Any of the four columns would work since ALL or NONE would be set to an empty string else 2 end, mailAddress desc,city desc ,state desc ,zip descSend us some data and expected results and we can probably get closer than this.

3

Whilst you can put an ORDER BY in a VIEW, as you have done "forcing it" with TOP 100 PERCENT, it is not guaranteed to work.

You need to have the ORDER BY in whatever the final SELECT is.

1 Like