REPLACE Multiple Spaces with One

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 29, 2009 at 5:41 pm

  #1085207

  C# Screw (11/29/2009)

  ---

  Oh but if I switch current database from Master to 'Another' database I get very differerent results...

  The collation thing again. It'd probably be fairer to tweak each method to use a binary collation.

  @jeff: If you have anything like the cold/flu I have at the moment, you have my sympathy!

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Jeff Moden

  SSC Guru

  Points: 1004361

  More actions

  November 29, 2009 at 6:05 pm

  #1085210

  Paul White (11/29/2009)

  ---

  C# Screw (11/29/2009)

  ---

  Oh but if I switch current database from Master to 'Another' database I get very differerent results...

  The collation thing again. It'd probably be fairer to tweak each method to use a binary collation.

  Easier too.... I forgot that you can't change the default collation on system databases.

  I'm going to setup a new database and rotate through the Latin_* and SQL_Latin* collations and see which ones are the fastest that don't actually require case sensitivity...

  @Jeff: If you have anything like the cold/flu I have at the moment, you have my sympathy!

  I think I've dodged the bullet with some meds and a whole lot of vitamin C... but the meds may be worse than the flu itself. I HAD to stay home from work the other day because I fell asleep at the wheel only about 5 miles away from home. Traffic was light so I didn't hit anyone or anything... curb woke me up. May have to change out the driver's seat in my pickup as a result... it took about 10 minutes before my butt cheeks finally let go of it. :hehe:

  --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)
  

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  November 29, 2009 at 11:27 pm

  #1085253

  Hi what abt this ?

  --===== Create and populate a test table

  IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  SELECT TOP 1000 CAST('1'+SPACE(7998)+'2' AS VARCHAR(8000)) AS SrtWord INTO #TestTable

  FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2

  PRINT REPLICATE('=',100)

  PRINT '========== Mitesh''s method =========='

  SET STATISTICS TIME ON

  SELECT

  REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(SrtWord,1,LEN(SrtWord)/2+1)))+' ' +LTRIM(RTRIM(SUBSTRING(SrtWord,LEN(SrtWord)/2+2,LEN(SrtWord))))

  ,' ','$#$'),'$$#',''),'$#$',' ')

  FROM

  #TestTable

  SET STATISTICS TIME OFF

  Regards,
  Mitesh OSwal
  +918698619998

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  November 29, 2009 at 11:31 pm

  #1085254

  Hi what abt this ?

  --===== Create and populate a test table

  IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  SELECT TOP 1000 CAST('1'+SPACE(7998)+'2' AS VARCHAR(8000)) AS SrtWord INTO #TestTable

  FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2

  PRINT REPLICATE('=',100)

  PRINT '========== Mitesh''s method =========='

  SET STATISTICS TIME ON

  SELECT

  REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(SrtWord,1,LEN(SrtWord)/2+1)))+' ' +LTRIM(RTRIM(SUBSTRING(SrtWord,LEN(SrtWord)/2+2,LEN(SrtWord))))

  ,' ','$#$'),'$$#',''),'$#$',' ')

  FROM

  #TestTable

  SET STATISTICS TIME OFF

  Regards,
  Mitesh OSwal
  +918698619998

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  November 29, 2009 at 11:40 pm

  #1085257

  --===== Create and populate a test table

  IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  SELECT TOP 1000 REPLACE(CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  + CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  + CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  + CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  + CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  + CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  +CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))+CAST('1'+SPACE(10)+'2' AS VARCHAR(8000))

  ,'1','asdadswde')

  AS SrtWord INTO #TestTable

  FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2

  PRINT REPLICATE('=',100)

  PRINT '========== Mitesh''s method =========='

  SET STATISTICS TIME ON

  SELECT

  LEN(srtword),

  REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(SrtWord,1,LEN(SrtWord)/2+1)))+' ' +LTRIM(RTRIM(SUBSTRING(SrtWord,LEN(SrtWord)/2+2,LEN(SrtWord))))

  ,' ','$#$'),'$$#',''),'$#$',' ')

  FROM

  #TestTable

  SET STATISTICS TIME OFF

  Regards,
  Mitesh OSwal
  +918698619998

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 30, 2009 at 1:52 am

  #1085273

  Colds must be in fashion at the moment! : my whole family has one:-)

  I must say that although my memory is a bit pixelated I believe I have kept to the install default.

  I did come accross this thread comment which may explain why we see non default databse collation setup (master and tempdb) on my UK locale machine:

  http://www.dbforums.com/microsoft-sql-server/997007-latin1_general_ci_as-vs-sql_latin1_general_cp1_ci_as.html

  In books online under "Specifying the Default Collation for an Instance of SQL Server" is says:

  Note: The Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. This may change in a future release.

  We have seen the case where a new database has been created (specifying no collation) and it doesn't get the server's default.

  E.g. the server is SQL_Latin1_General_Cp1_CI_AS and the database gets Latin1_General_CI_AS.

  From the help, new databases are supposed to get the server's collation if you don't specify one.

  I think the problem may be related to the fact that most PCs bought in the UK come set up with English US as default.

  When SQL Server is installed it was under English US, but when a database was created it had been changed to UK.

  The 'Challinors' database I in fact created from a client backup, hence that has sequence SQL_Latin1_General_Cp1_CI_AS.

  It would be interesting to understand why collation sequence SQL_Latin1_General_Cp1_CI_AS is better ?

  I found this when trying to find out the difference betwen them:

  http://www.sqlnewsgroups.net/sqlserver/t274-collation-question.aspx

  They compare non-Unicode (varchar/char) data using different rules. The

  Windows collation (Latin1_General_CI_AS) converts all non-Unicode data to

  Unicode and then uses the same Unicode string comparison rules that it

  uses for Unicode data. The benefit is that consistent rules are used for

  both Unicode and non-Unicode string comparisons. The SQL collation

  (SQL_Latin1_General_CP1_CI_AS) uses legacy SQL "sort order" rules to

  compare non-Unicode data, and a different set of sorting rules for

  Unicode data (the same Latin1_General rules that are used by

  Latin1_General_CI_AS). The benefit of this is backwards compatibility

  with older applications that may rely on the legacy SQL sort order for

  non-Unicode data.

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 30, 2009 at 2:08 am

  #1085277

  Mitesh

  could you please explain your code with comments ..?

  could you also explain the second post ..?

  Thanks

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  November 30, 2009 at 2:25 am

  #1085285

  sorry for non-comments,as I avoid to write comments as my english is not that good.

  In first post I just used LTRIM and RTRIM with divide string into 2 part,

  simmlilarly in Post 2 with different data having different space

  ,I want to check if the cpu usage is minimse.

  so with first post example result is far better

  ====================================================================================================

  ========== Mitesh's method ==========

  (1000 row(s) affected)

  SQL Server Execution Times:

  CPU time = 15 ms, elapsed time = 13 ms.

  ==================================================

  Simmilarly in post 2 with different spaces.

  (1000 row(s) affected)

  ====================================================================================================

  ========== Mitesh's method ==========

  (1000 row(s) affected)

  SQL Server Execution Times:

  CPU time = 500 ms, elapsed time = 597 ms.

  =====================================================

  Thanks,

  Mitesh oswal

  Regards,
  Mitesh OSwal
  +918698619998

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 30, 2009 at 3:45 am

  #1085326

  Mitesh Oswal (11/30/2009)

  ---

  sorry for non-comments,as I avoid to write comments as my english is not that good.

  In first post I just used LTRIM and RTRIM with divide string into 2 part,

  simmlilarly in Post 2 with different data having different space

  ,I want to check if the cpu usage is minimse.

  so with first post example result is far better

  ====================================================================================================

  ========== Mitesh's method ==========

  (1000 row(s) affected)

  SQL Server Execution Times:

  CPU time = 15 ms, elapsed time = 13 ms.

  ==================================================

  Simmilarly in post 2 with different spaces.

  (1000 row(s) affected)

  ====================================================================================================

  ========== Mitesh's method ==========

  (1000 row(s) affected)

  SQL Server Execution Times:

  CPU time = 500 ms, elapsed time = 597 ms.

  =====================================================

  Thanks,

  Mitesh oswal

  Mitesh

  I am not sure!

  I think you will do well if you happen by chance to split the string where there is a bug bunch of spaces in the middle of the string:

  'XXXXXXXXXXXXXX000000000000000000000000000000000000000XXXXXXXXXXXXXXXXXXXXXX'

  [If the O's are spaces]

  So by splitting in the middle of the spaces you are benefitting from speed on LTRIM & RTRIM over the speed of REPLACE.

  Although this works well with Jeff's test data where there are allot of spaces in the middle of the data, I am not sure how good it would be otherwise.

  But before running any more tests - I think I need to sort out this collation thing - as the (previous) test results might be misleading.

  I guess I will have to change tempdb & master collation and run tests again - but I might wait to hear of Jeff's test on best choice of collation sequence!!

  🙂

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• Jeff Moden

  SSC Guru

  Points: 1004361

  More actions

  November 30, 2009 at 5:23 am

  #1085346

  Mitesh Oswal (11/29/2009)

  ---

  Hi what abt this ?

  --===== Create and populate a test table

  IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  SELECT TOP 1000 CAST('1'+SPACE(7998)+'2' AS VARCHAR(8000)) AS SrtWord INTO #TestTable

  FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2

  PRINT REPLICATE('=',100)

  PRINT '========== Mitesh''s method =========='

  SET STATISTICS TIME ON

  SELECT

  REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(SrtWord,1,LEN(SrtWord)/2+1)))+' ' +LTRIM(RTRIM(SUBSTRING(SrtWord,LEN(SrtWord)/2+2,LEN(SrtWord))))

  ,' ','$#$'),'$$#',''),'$#$',' ')

  FROM

  #TestTable

  SET STATISTICS TIME OFF

  That works fine on that one example, Mitesh, but there's no guarantee that spaces will occur in the middle of the string where yhou split the string in two and you would only get a benefit of RTRIM/LTRIM if there were a large number of spaces right square in the middle of the string. You have to remember that it was just a "worse" case test on my part. The spaces could (and will) end up anywhere in the string.

  --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)
  

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  December 2, 2009 at 4:00 pm

  #1086816

  I am having some thoughts.... :unsure:

  T-SQL lacks any array construct - agreed?

  The last idea I had for space-replace was rather like an array processing task, but using Jeff's Tally to drive the iteration rather than looping in a while loop.

  This interests me, because if we consider a table variable to be the same as a multidimensional array, then jeffs Tally methos gives us a very efficient method of iterating through that array.

  In which case we can have pseudo array processing in T-SQL ...?

  As a remider here was the space replace idea I am thinking is 'close' to array processing:

  DECLARE @NewString VARCHAR(8000)

  SET @NewString = ''

  SELECT @NewString = @NewString + SUBSTRING(@MyString,N,1)

  FROM dbo.Tally t

  WHERE t.N <= DATALENGTH(@MyString)

  --Only append this character if this character and the next character are not both spaces

  AND (SUBSTRING(@MyString,N,1)+ ISNULL(SUBSTRING(@MyString,N+1,1),'')) <> ' '

  RETURN @NewString

  I have had glass of wine - actially no - stock check sais I have had a whole bottle - but u know inspiration can strike at any time - the key is to write it down (or post it) before you forget !!!:-)

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• Jeff Moden

  SSC Guru

  Points: 1004361

  More actions

  December 2, 2009 at 6:01 pm

  #1086838

  Actually, I have a "splitter" method I ginned up for the removal of spaces. It's much more effective than the single character split you've included but, guess what? It only come close to the method I explained in the article which also means that Michael's method still blows it away when it comes to compressing adjacent spaces.

  However, the method does have some performance merit when trying to "clean" a string of unwanted characters. Not sure I'll be able to get to it tonight but I'll see if I can arrange a demo for you.

  --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)
  

• Paul White

  SSC Guru

  Points: 150467

  More actions

  December 5, 2009 at 1:30 am

  #1088132

  Hey guys,

  I got a fantastic response to the connect item I filed concerning the collation performance thing. Check it out at http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=512459

  Extract:

  Jim Hogg MSFT

  ---

  I have assigned this report over to the Developer to investigate further. However, after chatting thru the issue, we reckon this perf difference is due to a bug.

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  December 5, 2009 at 7:08 am

  #1088150

  Paul White (12/5/2009)

  ---

  Hey guys,

  I got a fantastic response to the connect item I filed concerning the collation performance thing. Check it out at http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=512459

  Extract:

  Jim Hogg MSFT

  ---

  I have assigned this report over to the Developer to investigate further. However, after chatting thru the issue, we reckon this perf difference is due to a bug.

  Oh dude, wouldn't that be SWEET! Now watch them say "yep, it's a bug, but we aren't gonna fix it because ..." 🙁

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Paul White

  SSC Guru

  Points: 150467

  More actions

  December 5, 2009 at 7:55 am

  #1088156

  TheSQLGuru (12/5/2009)

  ---

  Oh dude, wouldn't that be SWEET! Now watch them say "yep, it's a bug, but we aren't gonna fix it because ..." 🙁

  True, true, true. But I'm staying positive on this one: even if it turns into a "won't fix" or "maybe in some future version", I'm happy that they've taken it seriously. If nothing else, I'll have learnt some new stuff about SQL internals, and that's always good!

  I was fully ready for a fob-off answer on this one, so I have to give huge credit for the work they've done so far. A lot of stuff gets posted on connect, much of it of very variable quality, so pretty happy about getting a well-researched answer. That's three times in a month or so now...a welcome trend.

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi