May 24, 2006 at 4:05 am
Is there a way i can clean up a database table
does anyone no a way to delete a carriage return, tab or new line in a sql table? From a column? Eg SQL column contains
APL
GRE
TOT
But which have a new line under them something messed on the front end but I need to clean it before I can fix it
May 24, 2006 at 4:22 am
see the T-SQL REPLACE function in the help file
May 25, 2006 at 6:14 am
Here's an example:
CREATE TABLE #test
(
id int IDENTITY(1,1)
, data varchar(30)
)
DECLARE @ascii tinyint
DECLARE @CR char(1) -- 13
, @LF char(1) -- 10
, @tab char(1) -- 9
, @VT char(1) -- 11
, @FF char(1) -- 12
SET @CR = Char(13)
SET @LF = Char(10)
SET @tab = Char( 9)
SET @VT = Char(11)
SET @FF = Char(12)
SET NOCOUNT ON
INSERT #test (data) VALUES ('A. Good data')
INSERT #test (data) VALUES ('B. Good data')
INSERT #test (data) VALUES ('C. Bad ' + @CR + 'data')
INSERT #test (data) VALUES ('D. Bad ' + @LF + 'data')
INSERT #test (data) VALUES ('E. Bad ' + @tab + 'data')
INSERT #test (data) VALUES ('F. Bad ' + @VT + 'data')
INSERT #test (data) VALUES ('G. Good data')
INSERT #test (data) VALUES ('H. Bad ' + @FF + 'data')
INSERT #test (data) VALUES ('I. Good data')
INSERT #test (data) VALUES ('J. Bad ' + @CR + 'data')
INSERT #test (data) VALUES ('K. Good data')
SET NOCOUNT OFF
-- List all rows
SELECT *
FROM #test
-- List only rows with "bad" data
SELECT *
FROM #test t
JOIN (SELECT DISTINCT number FROM master..spt_values WHERE number BETWEEN 0 AND 31) n
ON PatIndex('%' + Char(n.number)+ '%', t.data) > 0
-- Fix the "bad" data
SET NOCOUNT ON
SET @ascii = 0
WHILE @ascii < 32
BEGIN
UPDATE #test
SET data = REPLACE(data, Char(@ascii), '')
SET @ascii = @ascii + 1
END --while
SET NOCOUNT OFF
SELECT *
FROM #test
DROP TABLE #test
May 25, 2006 at 6:21 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply