count spaces between two strings

• ukumawat

  SSC Veteran

  Points: 250

  More actions

  May 29, 2009 at 5:39 am

  #134603

  hi all,

  is there any method to count number of spaces between two strings?

  thanks in advance.

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  May 29, 2009 at 12:31 pm

  #1002209

  I'm not entirely sure what you mean, but assuming you have a single string that comprises 2 sequences of characters that do not include any space characters separated by a variable number of space characters, then this expression will return the number of intervening space characters.

  LEN(RTRIM(LTRIM(String))) - LEN(REPLACE(String, ' ', ''))

  Note that, as it stands, it won't cope with other whitespace characters such as tabs.

  SELECT

  String,

  LEN(RTRIM(LTRIM(String))) - LEN(REPLACE(String, ' ', '')) AS InterveningSpaces

  FROM (

  SELECT 'ABC 123'

  UNION ALL

  SELECT ' ABCD 1234'

  UNION ALL

  SELECT ' ABCDE 12345 '

  ) TestData(String)

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  May 29, 2009 at 3:33 pm

  #1002302

  ukumawat (5/29/2009)

  ---

  hi all,

  is there any method to count number of spaces between two strings?

  thanks in advance.

  Andrew's method will work just fine, but I have to ask because this sounds like you might need to do something else... why do you need to count white space between blocks of characters?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)