December 2, 2009 at 8:52 am
Simple query of text field returns data that stops at a carriage return/line feed.
This is the query:
select notes from fincon_error where error_id = 4256
Return this:
GNLEVEL Pub N 10
Want to display all data...
Data in field is this:
GNLEVEL Pub N 10 ( 10.00000000) GCLAST Pub C "HANLEY" GCFIRST Pub C "PATRICK" MON Pub L .F. PRINTOPT Pub L .F. GCYR Pub C "SEI/Carnegie Mellon FA8721-05C-0002" GFISCALYR Pub C "2005" GDIRR Pub L .F. GCONTR Pub L .F. GPATHMSG Pub L .F. ACCESS Pub L .F. LOGIN Pub L .F. GDOCTRACK Pub L .F. GCSERVER Pub C "M:" GCLASTMOD2000 Pub L .F. GDLASTMODDATE2000 Pub L .F. GCLASTVOU2000 Pub L .F. GDLASTVOUDATE2000 Pub L .F. GCLASTMOD1995 Pub L .F. GDLASTMODDATE1995 Pub L .F. GCLASTVOU1995 Pub L .F. GDLASTVOUDATE1995 Pub L .F. GCACROBAT5 Pub L .F. GCACROBAT6 Pub L .T. GCLASTMOD2005 Pub C "P00098" GDLASTMODDATE2005 Pub D 11/18/2009 GCLASTVOU2005 Pub C "AV-52 " GDLASTVOUDATE2005 Pub D 12/12/2009 GCFINVIEW Pub C "L:" GCEMAIL Pub
December 2, 2009 at 8:54 am
December 2, 2009 at 8:58 am
SQL Server Management Studio
December 2, 2009 at 9:08 am
SSMS can be picky on what it displays. However, just because it is not displaying it doesn't mean that something else pulling the data will not. If you need to see it in SSMS, you can try changing from grid to file/text result modes and see if those help.
If not, you can do something like this(for character fields <8000 characters). This function relies on you having a tally/numbers table. If you do not have one, see the article in my signature for a full explanation of what it is and code to create one. Once both are in place, your code would change to this:
select dbo.SCA_TallyClean(notes) notes from fincon_error where error_id = 4256
If your field is really a text field, it'll be this:
select dbo.SCA_TallyClean(CAST(SUBSTRING(notes,1,8000) as varchar(8000))) notes
from fincon_error where error_id = 4256
Function:
CREATE FUNCTION SCA_TallyClean(
@A varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @B varchar(8000)
SET @B = '' -- Initialize @B
SET @A = REPLACE(REPLACE(@A,char(10),' '),char(13),' ') -- Remove Line Feed / Carriage Returns (The below would have removed them,
-- but I wanted to replace them with spaces for readability.)
SELECT @B = @B + SUBSTRING(@A,N,1)
FROM Tally
WHERE N <= DATALENGTH(@A)
AND
SUBSTRING(@A,N,1) LIKE '[a-zA-Z0-9. ,]' -- Remove everything but letters, numbers, spaces, period and comma.
RETURN REPLACE(REPLACE(@B,' ',' '),' ',' ') -- Removes some double spaces.
END
December 2, 2009 at 9:10 am
Man, I've used that script 2 times in 2 days! Look out Lowell, I'm creepin into your territory! :hehe:
December 2, 2009 at 9:29 am
Garadin,
Let me disgest this...
I have the info for a tally table.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply