April 8, 2011 at 2:41 am
Hi,
I have two tables:
1. Personnel
|Name | SecName
|John | M\123lkin
2. DiacriticalMarks - there I know that \123 means character "ü"
|Char | Numeric
|ü | \123
|á | \111
|é | \133
How can I replace automatically by some function all these "numeric" characters, when I can not use
SELECT REPLACE(Name, '\123', 'ü') AS Name
because I there are to many special characters which are numeric. The table is from ERP system which have special characters in numeric code and export them like this.
Thank you...
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply