testing null and space in sqlserver2008

Question

0.00/5 (No votes)

See more:

Hi my table has separate columns for house no,city,state.But i want adress as houseno,city,state problem is that is i have space entered in these fields i get ,,,
list so i want to test for space.

Posted 29-Jun-12 0:11am

Pratika05

Add a Solution

Comments

Sandeep Mewara 29-Jun-12 6:56am

Not clear. Are you saying your query result has spaces missing in output? Or you want to ignore the spaces?.

Please provide sample example and the query you are using.

Pratika05 29-Jun-12 7:05am

select
(IsNull(House_Name_No,'') +','+IsNull(City,'')+','+IsNull(State,'')
from emptable

this is my query if a record has no values for these i got ,,, but i want a space not ,,,

5 solutions

Solution 1

[Improved my solution........]May this query helps you..

SQL

select isnull(ltrim(rtrim(houseno)),'')+','+ isnull(ltrim(rtrim(city)),'') +','+ isnull(ltrim(rtrim(state)),'') as address from tablename

make it answer if you get your solution.

Thanks

Posted 29-Jun-12 0:16am

AshishChaudha

Updated 29-Jun-12 0:40am

v2

Comments

Pratika05 29-Jun-12 6:37am

no this does not solve my problem i am getting ,,, if fields are blank

AshishChaudha 29-Jun-12 6:41am

What you want if all fields are blank??

Pratika05 29-Jun-12 6:52am

if all fields are blank i need a space or nothng but not this ,,,

Solution 2

You can use
select isnull(houseno,'')+' '+isnull(city,'')+' '+isnull(state,'') as address

Posted 29-Jun-12 0:16am

dimpledevani

Comments

Pratika05 29-Jun-12 6:36am

no i want comma in between each part house no,city,state

Solution 4

Try this

SQL

SELECT (CASE WHEN LTRIM(RTRIM(HOUSENO)) IS NOT NULL THAN HOUSENO + ',' END
        CASE WHEN LTRIM(RTRIM(CITY)) IS NOT NULL THAN CITY + ',' END
        CASE WHEN LTRIM(RTRIM(STATE)) IS NOT NULL THAN STATE END ) AS ADDRESS
        FROM TABLENAME

Posted 29-Jun-12 1:05am

bhagirathimfs

v2

Comments

Pratika05 29-Jun-12 7:38am

incorrect syntax

bhagirathimfs 30-Jun-12 8:24am

ignore this answer sry for wrong answer :(

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sanwar_mal_jat · Accepted Answer · 2012-06-29T01:07:00

Hello dude may be helpful this code.

SQL

declare @house  nvarchar(200)
declare @city  nvarchar(200)
declare @state nvarchar(200)


set @house='50';
set @city='Himaat nagar';
set @state='Delhi';

select case when @house !='' then  @house else '' end  +  (case when @city !='' then case when @house!='' then ' , '+@city else @city end else ''  end)  +

 (case when @state !='' then case when @city!='' then ' , '+@state else @state end else ''end)

Use this code Where @house replace your table column house ,@city replace your table column City and @state replace your table column state and add and of query as (from tablename )

It is confirm proper work as i know your problem

Thanks

AshishChaudha · Accepted Answer · 2012-06-29T00:57:00

[updated the fields in else part of case..if forgot to replace in else part]try this..

I am not good in sql but I tried this and according to your requirement its perfectly working..

SQL

select case when isnull(ltrim(rtrim(houseno)),'') + ',' + isnull(ltrim(rtrim(city)),'') + ',' +  isnull(ltrim(rtrim(state)),'') =',,' then ' ' else isnull(ltrim(rtrim(houseno)),'') + ',' + isnull(ltrim(rtrim(city)),'') + ',' +  isnull(ltrim(rtrim(state)),'') end as address from [tablename]

make it answer if you get your solution.

Thanks

testing null and space in sqlserver2008

5 solutions

Solution 5

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0