April 8, 2011 at 4:44 am
Hi.
This table valued function will parse the contents of the secName field replacing you escape sequences with the desired character...
CREATE FUNCTION [dbo].[fn_DecodeDiacriticalMarks]
(
@EncodedString NVARCHAR(450)
)
RETURNS @DecodedMarks TABLE
(
decoded NVARCHAR(450)
)
AS
BEGIN
DECLARE @pos int = 0
DECLARE @DecodedString NVARCHAR(450) = @EncodedString
DECLARE @DecodedChar CHAR(1) = null
WHILE @pos < LEN(@DecodedString)
BEGIN
SELECT @DecodedChar = [char]
FROM DiacriticalMarks
WHERE [numeric] = SUBSTRING(@DecodedString,@pos,4)
IF @DecodedChar IS NOT NULL
BEGIN
SET @DecodedString = REPLACE(@DecodedString,
SUBSTRING(@DecodedString,@pos,4),
@DecodedChar)
END
SET @DecodedChar = NULL
SET @pos +=1
END
IF @EncodedString != @DecodedString
BEGIN
INSERT INTO @DecodedMarks(decoded)
VALUES(@DecodedString)
END
RETURN
END
You can then use OUTER APPLY to join the output back to your Personnel table.
SELECT p.name, ISNULL(f.decoded,p.secName)
FROM dbo.Personnel p
OUTER APPLY fn_DecodeDiacriticalMarks(p.secName) f
April 8, 2011 at 4:57 am
here's some sample data i slapped together:
with myERPMap(Letter,Code)
AS
(
SELECT N'ü','\123' UNION ALL
SELECT N'á','\111' UNION ALL
SELECT N'é','\133'
),
MyDataToFix (ThePhrase)
AS
(
SELECT 'Personn\133l \123s\111g\133' UNION ALL
SELECT 'sp\133ci\111l ch\111r\111ct\133rs' UNION ALL
SELECT '\111lph\111n\123m\133ric cod\133'
)
so far, i've only been able to do this with a loop; 'm trying to get better results, but this is the repalce in the loop funciton so far:
SELECT
REPLACE(MyDataToFix.ThePhrase,myERPMap.Code,myERPMap.Letter)
FROM MyDataToFix
CROSS JOIN myERPMap
Lowell
April 8, 2011 at 5:43 am
Hi,
thank you. This works!!!!!:-D
April 8, 2011 at 6:00 am
Great stuff.
Just make sure you test thoughly for edge cases, performance etc.
Ben
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply