Best way to find if string is whitespace

• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  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.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  June 25, 2019 at 8:36 pm

  #3657539

  Thank you!

• drew.allen

  SSC Guru

  Points: 77012

  More actions

  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

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".