clean unwanted characters in BI DATA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

clean unwanted characters in BI DATA

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-05-24 : 03:58:56

hi,

i have a lot of tables , and i want to clean all the characters in all the tables.
is the function (that i give the name of DB and it clean all tables in it)?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 04:19:54

I suspect not.
What do you mean by clean?

It sounds like you want to convert data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-05-24 : 04:42:20

suppose in one of rows(i column of table) , i have this string
'tes^@&t %stri)-n!g' i want to get test string

i want create function that over all clumns in all tables in DB
and replace(clean all unwanted characters )

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 07:15:34

You can eaily create a function to work on a column - but it would mean passing individual strings - i.e. running for each column and it would act on a row by row basis - i.e. a function call for each column and row.
Here is some code to do it - just change the patindex values.

You might also consider getting a list of rows with offending data - get the first unwanted character - replacing that in the column then looping until there are none left.
It depends on how much data and how many coumns/rows have data to change and how long you have to do it.

Of course it would be better to do it when thedata is inserted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-05-24 : 07:32:44

how i running for each column ,how can i get then columns each time

DECLARE @table_name VARCHAR(50)

DECLARE tableName_Cursor CURSOR FOR 
SELECT  table_name FROM   information_schema.tables
WHERE table_type='Base table' AND table_name<>'clean_chars'

OPEN tableName_Cursor
FETCH NEXT FROM tableName_Cursor INTO @table_name

WHILE @@fetch_status=0
BEGIN

...............................


FETCH NEXT FROM tableName_Cursor INTO @table_name
END
CLOSE tableName_Cursor
DEALLOCATE tableName_Cursor

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 08:08:43

Run this for a table then run the output (after creating your function)

with cte as
(
select Column_Name
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'mytbl'
and DATA_TYPE like '%CHAR%'
)
select 'update mytbl set ' +
stuff (
(
select ',[' + Column_Name + '] = dbo.myfunc(' + Column_Name + ')'
from cte
for xml path('')
)
,1,1,'')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-05-24 : 08:11:23

how i execute the output?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 08:26:49

In a query window?
How do you execute sql at the emoment?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-05-24 : 09:15:00

i run it and he wrote to me :
"Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

this query:

DECLARE @str varchar(max)
SET @str= ''
	  
		  
with cte as
(
select Column_Name
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'tablea'
and DATA_TYPE like '%CHAR%'
)

select @str= @str +'update tableA set ' + 
stuff (
(
select ',[' + Column_Name + '] = dbo.clean_data(' + Column_Name + ')'
from cte
for xml path('')
)
,1,1,'')
 
EXEC @str

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-24 : 09:37:00

Create a regular Expression and clean the data using the new mdx.RegEx-functions in Master data Services:

http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 10:19:06

Exactly what it says. add a semi-colon before the with
;with cte as

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-05-24 : 12:40:43

thanks,

can you help me with this error

Msg 203, Level 16, State 2, Line 4
The name 'update tableA set [ColumnA] = dbo.clean_data(ColumnA),[ColumnB] = dbo.clean_data(ColumnB)' is not a valid identifier

even i run that i get same error:

DECLARE @str varchar(max)
SET @str= 'update tableA set [ColumnA] = dbo.clean_data(ColumnA),[ColumnB] = dbo.clean_data(ColumnB)';
EXEC @str

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-24 : 13:41:47

change to EXEC (@str) with the brackets.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources