Wednesday, March 3, 2010

Have you dealt with ENTER key in INSERT table?

I have a client who uses the application that allows to insert data but also typing ENTER key in the middle. Well, the question is how do you want to store the data and later displaying on the client? He was not sure , so I showed him the below technique to display such kind of data....


--Create auxiliary table
SELECT TOP 8000 n = IDENTITY(INT)
INTO Numbers
FROM syscolumns s1, syscolumns s2 ;

CREATE TABLE test (col1 varchar(8000))

INSERT INTO test values ( 'Row----------1
Row--------2
Row--3
Row----4'
)

---See how it stores the data.
SELECT * FROM TEST

--Remove ENTER key
SELECT SUBSTRING(col1, n, CHARINDEX(CHAR(13), col1 + CHAR(13), n) - n)
FROM Numbers, test
WHERE SUBSTRING(CHAR(13) + col1, n, 1) = CHAR(13)
AND n < LEN(col1) + 1 ;

--Cleanup
DROP TABLE Numbers
DROP TABLE test

1 comment:

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!