Post reply

empty string

  • August 11, 2006 at 10:00 pm

    #191795

    what's the syntax for checking for empty string in a SQL statement?

    Len(VarcharCol)>1

    gave an error

    THanks,

    Sam

  • August 13, 2006 at 1:21 pm

    #654707

    It is the correct syntax. I think it is something else that is wrong.

    In what context are you using LEN(VarCarCol) > 1?

    N 56°04'39.16"
    E 12°55'05.25"

  • August 14, 2006 at 8:57 am

    #654885

    if VarCarCol is null, you wouldnt't  get an error when comparing null > 1 in SQL server; but in another application or programming language you would:

    in my sample table REFERENCENBR is null in all situations:

    select top 5 len(REFERENCENBR) from gmacidis where len(REFERENCENBR) > 1

    results: no data and message (0 row(s) affected)

    in VB however LEN(anything that evaulates asnull) > 1 raises an error;

     

    to fix it in the SQL, you could use LEN(ISNULL(VarCarCol,''))

    in a programming language, you might use a function like this:

     LEN(tnull(VarCarCol)) > 1?

    Public Function tnull(s as Variant) As String

        If IsNull(s) Then

            tnull = ""

        Else

            tnull = s

        End If

    End Function

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 15, 2006 at 3:39 am

    #655020

    The column is actually text rather than varchar. Here is my statement:

    Insert into dbo.CodeText(CodeID, Text1) (select CodeId, LongDescription from

    CodeLookup WHERE LongDescription IS NOT NULL AND Len(LongDescription)>1)

    and here is the error message:

    Argument data type text is invalid for argument 1 of len function.

    THanks,

    Sam

  • August 15, 2006 at 3:45 am

    #655021

    Thats the problem then, as the error message says the len function cannot be use on the data type text.

    What you can do is to cast the text to a varchar field to allow the use of len

    i.e. Len(cast(LongDescription as varchar(4000))) >1

     

     

  • August 15, 2006 at 6:48 am

    #655040

    Thanks Bill, Lowell and Peter.

  • August 16, 2006 at 1:16 am

    #655202

    or you can use the datalength function, which can be used on text fields

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2006 at 6:58 am

    #655243

    Thanks, Gila, that helped.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply