Post reply

Best way to find if string is whitespace

  • June 25, 2019 at 6:22 pm

    #3657489

    I appreciate it is a trifling question and yet.

    One of our sites organises its customers in small groups. Those groups are used for various purposes, including customer's address. When I generate invoice (many thousands a day) I check if the customer belongs to a certain group and then query if that group has an address. If it does not - I use customer's original address. Groups table is a very old one, with address 1, address 2 and address 3 lines defined as CHAR(40), so what I do is something IIF(LEN(address 1 + address 2 + address 3 = 0,customer.address 1,group.address 1 )). My questions are:

    1. What approach is better : LEN(address 1 + address 2 + address 3) or may be RTRIM(address 1 + address 2 + address 3) ? Asking as we are moving to a cloud, where we'll be charged differently and I want to reduce resources consumption where possible.
    2. When all three address columns are empty, LEN(address 1 + address 2 + address 3) returns 0, but if I try LEN(address 1 + address 2 + address 3 + 'test') I get 124 ? I understand why 124 : the address columns defined as CHAR(40) hence have 120 characters in total, but I would expect then LEN(address 1 + address 2 + address 3) to return 120 instead of 0, no?

    Thank you, as always!

    Attachments:
    You must be logged in to view attached files.

  • June 25, 2019 at 8:28 pm

    #3657528

    1 Don't concatenate the strings, that's more overhead, just check the len of each:

    LEN([address 1]) + LEN([address 2]) + LEN([address 3]) = 0 /*easier, to me*/

    OR

    (LEN([address 1]) = 0 AND + LEN([address 2]) = 0 AND LEN([address 3]) = 0) /*OK too*/

    2 LEN ignores trailing spaces.  When you concat non-blanks at the end, the entire length gets counted, since the other spaces are leading spaces.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 25, 2019 at 8:36 pm

    #3657539

    Thank you!

  • June 25, 2019 at 8:50 pm

    #3657548

    ScottPletcher wrote:

    1 Don't concatenate the strings, that's more overhead, just check the len of each: LEN([address 1]) + LEN([address 2]) + LEN([address 3]) = 0 /*easier, to me*/ OR (LEN([address 1]) = 0 AND + LEN([address 2]) = 0 AND LEN([address 3]) = 0) /*OK too*/ 2 LEN ignores trailing spaces.  When you concat non-blanks at the end, the entire length gets counted, since the other spaces are leading spaces.

    What's wrong with the following? [address 1] = '' AND [address 2] = '' AND [address 3] = ''.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • June 25, 2019 at 9:22 pm

    #3657560

    Nothing's wrong with it, per se.  I thought checking LEN was a bit more efficient, but either will do.  Edit: SQL may actually convert { = '' } to checking for { LEN = 0 } anyway.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 5 posts - 1 through 4 (of 4 total)

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