0

I need to see a certain substring of a field named RESPONSE_STREAM whose data type is TEXT. The DATALENGTH of RESPONSE_STREAM is 913,948 chars, which is way too long to see on my screen. I need to retrieve the rest of the field starting from the first occurrence of 'FLEKSHER', in order to determine if there's a problem with my data. (It's hard to be more specific than that given the nature of what I'm doing--HIPAA data, etc.)

I've tried selecting that field into a temp table and right-clicking on the field to copy it and paste it into a Word document, but the whole field doesn't get copied.

This is the SQL query that selects the field from the table it resides in to a temp table: 

SELECT RESPONSE_STREAM INTO #tmp_foo 
FROM CMS_BATCH_RESPONSE 
WHERE BATCH_UUID ='5F61FB04-8679-48F2-B747-30C9837C85EC' AND
      RESPONSE_TYPE_CODE = 835 AND
      RESPONSE_STREAM LIKE '%FLEKSHER%'

Doing this told me there are 918,394 chars in the entire field: select DATALENGTH (RESPONSE_STREAM) from #tmp_foo

I expected the following SELECT to tell me where the string 'FLEKSHER' starts in the TEXT field--but it returned 0: 

SELECT CHARINDEX ('FLEKSHER', RESPONSE_STREAM, 1) FROM #tmp_foo

Is there any way I can retrieve a substring from this very long TEXT field?

JustLv
  • 1
  • 1
  • did you try using charindex without specifying the start location (708)? Are you sure the text to find is FLEKSHER? – Andres2142 Aug 14 '19 at 21:42
  • Just a guess, but I suspect you may want to consider https://stackoverflow.com/questions/11039552/sql-server-field-getting-truncated – John Cappelletti Aug 14 '19 at 21:45
  • Andres2142, thanks for spotting that--I took out the 708. It was just something I was trying. – JustLv Aug 15 '19 at 18:20

1 Answers1

0

Well, according to microsoft docs sql server, the CHARINDEX should work using on a variable to search for, not from a table. I would do this and see if it works: 

DECLARE 
       @HUGESTRING VARCHAR(MAX);

SET @HUGESTRING = (SELECT RESPONSE_STREAM
                   FROM CMS_BATCH_RESPONSE
                   WHERE BATCH_UUID ='5F61FB04-8679-48F2-B747-30C9837C85EC' AND
                         RESPONSE_TYPE_CODE = 835);

SELECT CHARINDEX('FLEKSHER', @HUGESTRING);
Andres2142
  • 2,622
  • 2
  • 14
  • 19
  • Thank you, Andres2142--that did the trick! I upvoted your response, but it's not being displayed because I haven't posted enough. – JustLv Aug 15 '19 at 18:29