May 2009 - Posts

Removing unwanted characters

 

Sometimes
when you import data from other system, you may want to clean up data by removing unwanted characters

You can use Replace function. Suppose you want to remove the character ^ from the string

declare @str varchar(100)
set @str='test^ string'
select @str=replace(@str,'^','')

Select @str

But what if you have lot of characters to be removed?
You can have a seperate
table that has the set of characters to be removed and use a function

--Create test data

create table #data (data varchar(100))

insert #data
select 'tes^@&t %stri)-n!g' data union all
select
'))aaer***********)' union all
select
'&^&&hsdf()' union all
select
'jj&wk' union all
select
')hw*pa' union all
select
'No&@#$53*24,Mai()$n R--!oad'

--Create table that has all set of characters to be removed

create table clean_chars (char_id int identity(1,1),chars char(1))

insert into clean_chars (chars)
select '^' as repl union all
select ')' union all
select
'(' union all
select
'&' union all
select
'*' union all
select
'%' union all
select
'@' union all
select
'-' union all
select
',' union all
select
'#' union all
select
'$' union all
select
'#' union all
select
'!'

 

--Create a function that removes all those characters from data

create function clean_data
(
@data varchar(100)
)
returns varchar(1000)

as

begin
        declare @char_id varchar(1000),@clean_data varchar(1000)
        set @clean_data=@data
        select @char_id=min(char_id) from clean_chars

        while @char_id is not null
        begin
                select @clean_data=replace(@clean_data,chars,'') from clean_chars where char_id=@char_id
                select @char_id= min(char_id) from clean_chars where char_id>@char_id
        end

return @clean_data

end

--Run the query

select data,dbo.clean_data(d.data) as clean_data from #data as d

Posted by Madhivanan with 2 comment(s)
Filed under: , ,

How to find size of backup files in a folder?

I was asked to findout total size of all the backup files in the folder
As I didnt have access to view the folder of the server, I used this.
I hope it may be helpful

create table #t(files varchar(1000))

Insert into #t
EXEC master..xp_cmdshell 'dir e:\backup'

select
       
substring(size_files,charindex(' ',size_files)+1 ,len(size_files))as backup_file,
       
cast(substring(size_files,1,charindex(' ',size_files)-1) as money)/1024/1024 as file_size_MB
from
(
        select files,ltrim(substring(files,patindex('%[ AP ]M%',files)+2,len(files))) as size_files 
        from #t where files like '%bak'
) as t

drop table #t

Posted by Madhivanan with no comments

Import from Text file - Single column to multiple columns

One of the users asked this question in the SQL forum

I have data in the text as follows


AccountNo: 00234543
AccountName: Kickser
City: Chicago
Country:USA
AccountNo: 00234543
AccountName: Annis
City: Seatle
Country:USA
AccountNo: 12234456
AccountName: pargenezzer
City: NYC
Country:USA
...
....
.....
AccountNo: 12233477
AccountName: PILIP
City: Edison

Country:USA

How do I store data to SQL Server table that has columns AccountNo,AccountName,City which is normalised?

Here is one of the methods that does it

--Create statging table to load data

create table test_data(data varchar(1000),value varchar(1000))

GO

--Load data from text file to staging table

BULK INSERT test_data FROM 'file_path'  WITH
(
FIELDTERMINATOR =':',
ROWTERMINATOR ='\n'
)

GO

--Add identity column

alter table test_Data add id int identity(1,1)

GO

--Do process and convert each column value to multiple columns

declare @count int,@sql varchar(8000),@columns varchar(1000),@i int

select @count=count(distinct data) from test_Data
select @sql='', @i=1

while @i<=@count
Begin
       
select @sql=@sql+'select id,value as value'+cast(@i as varchar(10))+' from test_Data where id%'+cast(@count as 
        varchar
(2))+'='+cast(case when @i=@count then 0 else @i end as varchar(2))+') as t'+cast(@i as varchar(2))+
       
case when @i>1 then ' on t1.id+'+cast(@i-1 as varchar(2))+'='+'t'+cast(@i as varchar(2))+'.id' else '' end+
       
case when @i<>@count then ' inner join (' else '' end,
       
@columns=coalesce(@columns+',','')+'t'+cast(@i as varchar(2))+'.value'+cast(@i as varchar(10))

        select @i=@i+1

End

--Show the result

select
@sql='select '+@columns+' from

('+@sql

exec(@sql)

Now the result can be copied to another normalised table