CODEWITHSUNDEEP
sql-server
Peter Kellner
Peter Kellner

Reputation: 15478

In SQL Server, how to have select return empty string and not null when null

I have a nullable varchar and even when it is null, I want my select to return an empty string and not null. That is

SELECT FIRST, LAST 
FROM CUSTOMER

What I want is if FIRST is null, I want the return to be ""

Upvotes: 4

Views: 16807

Answers (5)

Rabee
Rabee

Reputation: 1

CREATE FUNCTION [dbo].[ParamParserFn]
    (
      @delimString VARCHAR(255) ,
      @delim CHAR(1)
    )
RETURNS @paramtable TABLE
    (
      [Index] INT ,
      [Token] INT
    )
AS 
    BEGIN

        DECLARE @len INT ,
            @index INT ,
            @nextindex INT ,
            @counter INT ,
            @data VARCHAR(50)

        SET @len = DATALENGTH(@delimString)
        SET @index = 0
        SET @nextindex = 0
        SET @counter = 0


        WHILE ( @len + 1 > @index ) 
            BEGIN

                SET @nextindex = CHARINDEX(@delim, @delimString, @index)

                IF ( @nextindex = 0 ) 
                    SET @nextindex = @len + 1



                INSERT  @paramtable
                        SELECT  @counter ,
                                SUBSTRING(@delimString, @index,
                                          @nextindex - @index)


                SELECT  @index = @nextindex + 1 ,
                        @counter = @counter + 1
            END


        RETURN
    END

Upvotes: 0

user751651
user751651

Reputation: 

SELECT COALESCE(FIRST, ''), LAST FROM CUSTOMER

would work also

Upvotes: 1

AlexCuse
AlexCuse

Reputation: 18296

You can also use

SELECT COALESCE(FIRST, ''), LAST FROM CUSTOMER

This has the advantage of being more portable (COALESCE is part of the SQL Standard, ISNULL isn't)

You can also use an arbitrary number of arguments, like

SELECT COALESCE(FIRST, SECOND, THIRD, '')...

And COALESCE will use the first non-null value encountered

Upvotes: 6

Blorgbeard
Blorgbeard

Reputation: 103437

SELECT isnull(FIRST,'') AS FIRST, LAST FROM CUSTOMER

Upvotes: 6

Sparky
Sparky

Reputation: 15085

select isNull(First,'') from customer

Upvotes: 1

Related Questions