API tools faq
paste
Advertisement
SqlQuantumLeap

Get 1000 chars max, up to final period, else to final return

SqlQuantumLeap
Nov 16th, 2015
404
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 10.49 KB | None | 0 0
raw download clone embed print report
  1. -- this relates to the following DBA.StackExchange answer:
  2. -- http://dba.stackexchange.com/questions/121206/string-function-to-get-left-1000-but-only-to-last-period-or-carriage-return/121272#121272
  3.  
  4. SET NOCOUNT ON;
  5.  
  6. ------------------------------------------------------------------------------------
  7. -- TEST SETUP
  8. ------------------------------------------------------------------------------------
  9.  
  10. DECLARE @TestCases TABLE (TestName VARCHAR(100) NOT NULL, TestData NVARCHAR(4000) NULL);
  11. INSERT INTO @TestCases (TestName, TestData) VALUES
  12.   ('Original text', N'Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure. Four need spot ye said we find mile. Are commanded him convinced dashwoods did estimable forfeited. Shy celebrated met sentiments she reasonably but. Proposal its disposed eat advanced marriage sociable. Drawings led greatest add subjects endeavor gay remember. Principles one yet assistance you met impossible. The who arrival end how fertile enabled. Brother she add yet see minuter natural smiling article painted. Themselves at dispatched interested insensible am be prosperous reasonably it. In either so spring wished. Melancholy way she boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself waited to. Always parish tastes at as mr father dining at. Led ask possible mistress relation elegance eat likewise debating. By message or am nothing amongst chiefly address. The its enable direct men depend highly. Ham windows sixteen who inquiry fortune demands. Is be upon sang fond must shew. Really boy law county she unable her sister. Feet you off its like like six. Among sex are leave law built now. In built table in an rapid blush. Merits behind on afraid or warmly.'),
  13.   ('Original text chopped at 1000 chars', N'Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure. Four need spot ye said we find mile. Are commanded him convinced dashwoods did estimable forfeited. Shy celebrated met sentiments she reasonably but. Proposal its disposed eat advanced marriage sociable. Drawings led greatest add subjects endeavor gay remember. Principles one yet assistance you met impossible. The who arrival end how fertile enabled. Brother she add yet see minuter natural smiling article painted. Themselves at dispatched interested insensible am be prosperous reasonably it. In either so spring wished. Melancholy way she boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself waited to. Always parish tastes at as mr father dining at. Led ask possible mistress relation elegance eat likewise debating. By message or am nothing amongst chiefly address. The its en'),
  14.   ('Original text with period at position 1001', N'Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure. Four need spot ye said we find mile. Are commanded him convinced dashwoods did estimable forfeited. Shy celebrated met sentiments she reasonably but. Proposal its disposed eat advanced marriage sociable. Drawings led greatest add subjects endeavor gay remember. Principles one yet assistance you met impossible. The who arrival end how fertile enabled. Brother she add yet see minuter natural smiling article painted. Themselves at dispatched interested insensible am be prosperous reasonably it. In either so spring wished. Melancholy way she boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself waited to. Always parish tastes at as mr father dining at. Led ask possible mistress relation elegance eat likewise debating. By message or am nothing amongst chiefly address. The its en.able direct men depend highly. Ham windows sixteen who inquiry fortune demands. Is be upon sang fond must shew. Really boy law county she unable her sister. Feet you off its like like six. Among sex are leave law built now. In built table in an rapid blush. Merits behind on afraid or warmly.'),
  15.   ('Original text with period at position 1000', N'Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure. Four need spot ye said we find mile. Are commanded him convinced dashwoods did estimable forfeited. Shy celebrated met sentiments she reasonably but. Proposal its disposed eat advanced marriage sociable. Drawings led greatest add subjects endeavor gay remember. Principles one yet assistance you met impossible. The who arrival end how fertile enabled. Brother she add yet see minuter natural smiling article painted. Themselves at dispatched interested insensible am be prosperous reasonably it. In either so spring wished. Melancholy way she boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself waited to. Always parish tastes at as mr father dining at. Led ask possible mistress relation elegance eat likewise debating. By message or am nothing amongst chiefly address. The its e.nable direct men depend highly. Ham windows sixteen who inquiry fortune demands. Is be upon sang fond must shew. Really boy law county she unable her sister. Feet you off its like like six. Among sex are leave law built now. In built table in an rapid blush. Merits behind on afraid or warmly.'),
  16.   ('Original text chopped at 1000 chars with period at position 1000', N'Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure. Four need spot ye said we find mile. Are commanded him convinced dashwoods did estimable forfeited. Shy celebrated met sentiments she reasonably but. Proposal its disposed eat advanced marriage sociable. Drawings led greatest add subjects endeavor gay remember. Principles one yet assistance you met impossible. The who arrival end how fertile enabled. Brother she add yet see minuter natural smiling article painted. Themselves at dispatched interested insensible am be prosperous reasonably it. In either so spring wished. Melancholy way she boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself waited to. Always parish tastes at as mr father dining at. Led ask possible mistress relation elegance eat likewise debating. By message or am nothing amongst chiefly address. The its e.'),
  17.   ('No period but added a Carriage Return', N'Uaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' + CHAR(13) + 'aaaaaaaaaaaaaaaaaaaaaBCD'),
  18.   ('Period and Carriage Return, but Carriage Return is last', N'Uaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaa.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' + CHAR(13) + 'aaaaaaaaaaaaaaaaaaaaaBCD');
  19.  
  20.  
  21. ------------------------------------------------------------------------------------
  22. -- TEST CODE
  23. ------------------------------------------------------------------------------------
  24.  
  25. SELECT TestName,
  26.        LEN(TestData) AS [OriginalLength],
  27.        CHARINDEX('.', REVERSE(LEFT(TestData, 1000))) AS [LocationOfPeriod],
  28.  
  29.        '---' AS [---],
  30.            LEFT(LEFT(TestData, 1000),
  31.                 1001 - CHARINDEX('.', REVERSE(LEFT(TestData, 1000)))) AS [1000CharsUpToFinalPeriod],
  32.        LEN(LEFT(LEFT(TestData, 1000),
  33.                 1001 - CHARINDEX('.', REVERSE(LEFT(TestData, 1000))))) AS [LengthOf1000CharsUpToFinalPeriod],
  34.  
  35.        '---' AS [---],
  36.            LEFT(LEFT(TestData, 1000),
  37.                 1001 - PATINDEX('%[.' + CHAR(13) + ']%', REVERSE(LEFT(TestData, 1000)))) AS [1000CharsUpToFinalPeriodOrReturn],
  38.        LEN(LEFT(LEFT(TestData, 1000),
  39.                 1001 - PATINDEX('%[.' + CHAR(13) + ']%', REVERSE(LEFT(TestData, 1000))))
  40.           ) AS [LengthOf1000CharsUpToFinalPeriodOrReturn],
  41.  
  42.        '---' AS [---],
  43.            LEFT(LEFT(TestData, 1000), 1001 - IIF(CHARINDEX('.', REVERSE(LEFT(TestData, 1000))) > 0,
  44.                                                  CHARINDEX('.', REVERSE(LEFT(TestData, 1000))),
  45.                                                  CHARINDEX(CHAR(13), REVERSE(LEFT(TestData, 1000))))
  46.                ) AS [1000CharsUpToFinalPeriodElseReturn],
  47.        LEN(LEFT(LEFT(TestData, 1000), 1001 - IIF(CHARINDEX('.', REVERSE(LEFT(TestData, 1000))) > 0,
  48.                                                  CHARINDEX('.', REVERSE(LEFT(TestData, 1000))),
  49.                                                  CHARINDEX(CHAR(13), REVERSE(LEFT(TestData, 1000))))
  50.                )) AS [LengthOf1000CharsUpToFinalPeriodElseReturn]
  51. FROM   @TestCases;
  52.  
  53. ------------------------------------------------------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!