text field holding employee resumes

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 2000 Forums

Transact-SQL (2000)

text field holding employee resumes

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2005-09-13 : 18:00:28

Hello All,

I have a table with a field named ResumeText(datatype: Text) that holds the formatted resume text...

does anyone has a procedure that kind of cleans this field because when it contains some weird characters the front end that displays this field is failing...

i came up with what a i can say a bad procedure..just testing to replace the weird characters with spaces...but may be this is not what i want to do...may i need these characters sometime which signify something instead of a corrupt character....how can deal with this situation...how to clean up this field...the below is my procedure...please suggest a new method if my procedure looks crap...and it is very inefficient and takes a long time...

[CODE]
CREATE PROCEDURE ResumeClean_Test1
@Employee varchar(20),
@ResumeCategory varchar(10)
AS

declare @ptr binary(16) ,
@offset int ,
@id int,
@DataLength int,
@CharCode int,
@Char varchar(1)

Select
*
into #ResumeClean
from mydb.dbo.EMResume
where Employee = @Employee
and ResumeCategory = @ResumeCategory

Delete from mydb.dbo.EMResume
where Employee = @Employee
and ResumeCategory = @ResumeCategory

insert into mydb.dbo.EMResume
(
Employee
, ResumeCategory
, ResumeText
, CreateUser
, CreateDate
, ModUser
, ModDate
)
Select Employee
, ResumeCategory
, ' '
, CreateUser
, CreateDate
, ModUser
, ModDate
from #ResumeClean

select @ptr = textptr(ResumeText)
from mydb.dbo.EMResume
where
ResumeCategory = @ResumeCategory
and employee = @Employee

Select @DataLength = DataLength(ResumeText)
from #ResumeClean

set @id = 1
While @id < @DataLength
Begin
Select @CharCode = Ascii(substring(ResumeText,@id,1))
from #ResumeClean
if @CharCode < 32 or @CharCode > 127 select @CharCode = 32
Set @Char = Char(@CharCode)
select @Char, @CharCode
Updatetext mydb.dbo.EMResume.ResumeText @ptr @id 0 @Char
set @id = @id + 1
End

drop table #ResumeClean

GO
[/code]

if someone has a procedure handy for this kind of situation, please post it here...

thanks

KLang23
Posting Yak Master

115 Posts

Posted - 2005-09-13 : 18:11:54

Hi,

It may be a moot point, but you may want to consider storing the resumes as text files and using SQL to store the path and name of the text file. It's a lot easier than manipulating text-typed data.

Sorry I don't have an alternate solution to replace the unwanted characters in the text-typed column.

dupati1
Posting Yak Master

123 Posts

Posted - 2005-09-13 : 19:50:02

KLang23,
Thanks for the reply. I will also look into your suggestion...if someone else has any other suggestions...please post....thanks in advance

hey1
Starting Member

1 Post

Posted - 2005-09-14 : 08:19:22

go here:

http://www.sql.ru/forum/actualthread.aspx?tid=216816

dupati1
Posting Yak Master

123 Posts

Posted - 2005-09-14 : 08:24:57

can anyone explain..what is this query doing below...i have a text field...and the below is for the string...i can use it for text fields...


SELECT *
INTO #temp
FROM (
SELECT ' ', SPACE(1) UNION ALL
SELECT '&', SPACE(0) UNION ALL
SELECT '-', SPACE(0) UNION ALL
SELECT ',', SPACE(0) -- add as many as needed
) D(old, new)




DECLARE @Str VARCHAR(100)
SET @Str = 'AT&TWIRELESS-REDMOND,WA'
UPDATE #temp
SET @Str = REPLACE(@Str, Old, New)
PRINT @Str

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources