SQL query
-
Hi! I am new to this forum.
I need to update spelling errors in a table. It looks like this:
ID Value
-- -----
xyz 'hello my friend'
xyz 'helo my friend'
xyz 'helo my firend'
abc 'city center'
abc ' citty senter'
....
Every first row is correctely spelled. I want to assign the correct value to the incorrect ones.
How do I do this? I had in mind somethign like the following but it does of course not work:
update spellerrors t
set value=t.value
from (select distinct ID,Value from spellerrors
where t.id=id)
PLease fellows, how do you do this?
-
I would recommend a function. You can use that to fix your current data and then call it in your code to fix spelling errors from the front-end when the data user enters data.
This example calls a table which has the mis-spellings of interest and then replaces them with what you want. This may end up being a large table for you and hopefully someone has come across this and knows a good way to populate this table.
Here is an example of a function which calls the Spelling table. Alter it to your needs...
UPDATE spellerrors t SET
value = dbo.ReplaceSpelling( t.value)
FROM( SELECT DISTINCT IF, Value FROM spellerrors WHERE t.id = id)
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpelling') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.ReplaceSpelling
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.ReplaceSpelling ( @ValueToAlter AS varchar(2000)) RETURNS varchar(2000)
AS
BEGIN
DECLARE @CurrentID integer,
@MaxID integer,
@SearchForValue varchar(100),
@ReplacementValue varchar(100)
SELECT @CurrentID = (SELECT MIN( RowID) FROM Spelling)
SELECT @MaxID = (SELECT MAX( RowID) FROM Spelling)
SELECT @SearchForValue = (SELECT SearchForValue FROM Spelling WHERE RowID = @CurrentID)
SELECT @ReplacementValue = (SELECT ReplacementValue FROM Spelling WHERE RowID = @CurrentID)
WHILE @CurrentID <= @MaxID
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, @SearchForValue, @ReplacementValue)
SELECT @CurrentID = (SELECT MIN( RowID) FROM Spelling WHERE RowID > @CurrentID)
SELECT @SearchForValue = (SELECT SearchForValue FROM Spelling WHERE RowID = @CurrentID)
SELECT @ReplacementValue = (SELECT ReplacementValue FROM Spelling WHERE RowID = @CurrentID)
END
RETURN ( @ValueToAlter )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I wasn't born stupid - I had to study.
-
Ooops. I just thought about this and you will have to do a bit more coding on the update to split each word up. You should be able to do that with CHARINDEX using the spaces between the words to grab each word individually....
You would probably need to integrate something like this into the function:
DECLARE @Delimiter char(1),
@LeftDelimter smallint,
@ChangedWord varchar(50),
@StringToCheck varchar(2000),
@SingleWord varchar(2000),
@FinalString varchar(2000)
CREATE TABLE #List( NewString varchar(2000) NOT NULL)
IF NOT( @StringToCheck IS NULL OR @StringToCheck = '0' OR LEN( RTRIM( @StringToCheck)) < 1)
BEGIN
SELECT @LeftDelimter = 1,
@Delimiter = CHAR(32),
@SingleWord = @Delimiter + @StringToCheck + @Delimiter
WHILE CHARINDEX( @Delimiter, @SingleWord, @LeftDelimter + 1) > 0
BEGIN
SELECT @ChangedWord = SUBSTRING( @SingleWord, @LeftDelimter + 1, CHARINDEX( @Delimiter, @SingleWord, @LeftDelimter + 1) - ( @LeftDelimter + 1))
IF LTRIM( RTRIM( @ChangedWord)) <> ''
BEGIN
SELECT @FinalString = @FinalString + CHAR(32) + @ChangedWord
END
SELECT @LeftDelimter = CHARINDEX( @Delimiter, @SingleWord, @LeftDelimter + 1)
END
SELECT @FinalString
END
SET NOCOUNT OFF
I wasn't born stupid - I had to study.
-
Hi Farrell! Thank you for your suggestion. It looks good and workable.
I will come back when I have tried this out.
Regards
Ivan
-
You betcha. I just hope someone else has used this method and has some values to populate your table. That could end up being a long, long list....
Good luck and look forward to hearing your solution.
I wasn't born stupid - I had to study.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply