Order by issue with HTMLencoded Surnames ... and some not

I am working on quite an old MS SQL server as the back of a web application. I am making queries on a table which includes ‘surnames’ (Spanish),  some of which are HTMLencoded and some are not. This does not matter on the web as the browser decodes and displays the text whether it is HTMLencoed or not. However, when I want to Order By ‘surnames’,  I get unexpected results.

Surnames ------
López                   << Not encoded
L&#243;pez       <<encoded
Lopez                  <<misspelled

I really need a robust UDF or built in function which could parse the individual table row on a sort of HTMLdecode() and using the resulting value to decide the Sort By. That was I would simply call that function to elevate my listing problems.

I appreciate any help, advice or pointers. Perhaps there is a better way!

Many thanks for your help in advance.

Kind regards

Abiel M de Groot Sanders
LVL 5
Abiel de GrootDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Please supply some real sample data to test against.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a htmldecode function found here, updated to use NVARCHAR instead of VARCHAR, and MAX as size limit instead of the 8000 (4000 for nvarchar)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20749 
CREATE FUNCTION dbo.f_HTMLDecode (@vcWhat nvarchar(max))
RETURNS nvarchar(max) AS  
BEGIN 
DECLARE @vcResult nvarchar(max)
DECLARE @vcCrLf nvarchar(2)
DECLARE @siPos smallint,@vcEncoded nvarchar(7),@siChar smallint

set @vcCrLF=char(13) + char(10)

select @vcResult=@vcWhat
select @siPos=PatIndex('%&#___;%',@vcResult)
WHILE @siPos>0
  BEGIN
      select @vcEncoded=substring(@vcResult,@siPos,6)
      select @siChar=cast(substring(@vcEncoded,3,3) as smallint)
      select @vcResult=replace(@vcResult,@vcEncoded,nchar(@siChar))
      select @siPos=PatIndex('%&#___;%',@vcResult)
  END

select @siPos=PatIndex('%&#____;%',@vcResult)
WHILE @siPos>0
  BEGIN
      select @vcEncoded=substring(@vcResult,@siPos,7)
      select @siChar=cast(substring(@vcEncoded,3,4) as smallint)
      select @vcResult=replace(@vcResult,@vcEncoded,nchar(@siChar))
      select @siPos=PatIndex('%&#____;%',@vcResult)
  END

select @vcResult=replace(@vcResult,'"','"')
select @vcResult=replace(@vcResult,'&','&')
select @vcResult=replace(@vcResult,'©','©')
select @vcResult=replace(@vcResult,'«','«')
select @vcResult=replace(@vcResult,'»','»')
select @vcResult=replace(@vcResult,'¼','¼')
select @vcResult=replace(@vcResult,'½','½')
select @vcResult=replace(@vcResult,'¿','¿')

select @vcResult=replace(@vcResult,'<P>',@vcCrLf)

return @vcResult
END

Open in new window

so you can already
ORDER BY dbo.f_HTMLDecode(yourfield)

now, in regards to you swedisch or other special characters, you need to specify which character set to apply... using COLLATE : http://technet.microsoft.com/en-us/library/ms184391.aspx
ORDER BY dbo.f_HTMLDecode(yourfield)  COLLATE Traditional_Spanish_ci_ai

hope this helps
0
Brian CroweDatabase AdministratorCommented:
Yeah i found the same one but make sure you read further down in the post:

replace the replace sections with:

select @vcResult=replace(@vcResult,'&quot;','"')
select @vcResult=replace(@vcResult,'&amp;','&')
select @vcResult=replace(@vcResult,'&copy;','©')
select @vcResult=replace(@vcResult,'&laquo;','«')
select @vcResult=replace(@vcResult,'&raquo;','»')
select @vcResult=replace(@vcResult,'&frac14;','¼')
select @vcResult=replace(@vcResult,'&frac12;','½')
select @vcResult=replace(@vcResult,'&iquest;','¿')

If author posts some real data we can tweak this to work against his data instead of just guessing.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Abiel de GrootDeveloperAuthor Commented:
Sorry, I had to pop out. Looking at these now.

A.
0
Abiel de GrootDeveloperAuthor Commented:
Sorry ... I am a bit Green in this section. When you say, 'Post some real data' would you like me to script the table? Or what would you like.

A.
0
Brian CroweDatabase AdministratorCommented:
If you could either attach a file or just list a dozen or so cases that cover the different permutations that would be sufficient.  If you could do it in the form of a query so we don't need to compose it ourselves even better.

CREATE TABLE testHTML
(
   ... (if there are many columns here than just include the ones that matter)
)

INSERT INTO testHTML (encodedColumn)
SELECT 'valueHTML1'
UNTION ALL
SELECT 'valueHTML2'
UNION ALL
SELECT 'valueHTML3'
...
0
Abiel de GrootDeveloperAuthor Commented:
Ok ... give me 5 mins

I will copy some values as they appear in the remote SQL.

A.
0
Abiel de GrootDeveloperAuthor Commented:
Hows This?
----------------------------------------------

/****** Object:  Table [dbo].[TestTable_01]    Script Date: 09/17/2013 18:06:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable_01](
      [Rec_ID] [int] IDENTITY(1,1) NOT NULL,
      [Surname] [nvarchar](100) NULL,
 CONSTRAINT [PK_TestTable_01] PRIMARY KEY CLUSTERED
(
      [Rec_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TestTable_01] ON
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (1, N'L&#243;pez Luquin')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (2, N'Lopez Jauregui')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (3, N'Logroño')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (4, N'Corral Gonz&#225;lez')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (5, N'Dom&#237;nguez Ugalde')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (6, N'L&#243;pez xxx')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (7, N'Lopez xxx')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (8, N'López xxx')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (9, N'Pereda Fern&#225;ndez')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (10, N'Loro&#241;o S&#225;nchez')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (11, N'Lorono Sánchez')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (12, N'abbey  Sánchez')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (13, N'López García')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (14, N'López Urarte')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (15, N'Garc&#237;a franks')
INSERT [dbo].[TestTable_01] ([Rec_ID], [Surname]) VALUES (16, N'Garcia franks')
SET IDENTITY_INSERT [dbo].[TestTable_01] OFF
0
Brian CroweDatabase AdministratorCommented:
It seems to boil down to a series of REPLACE statements.  Try the function below and let me know if that is sufficient or if I'm missing something...

IF OBJECT_ID('dbo.udf_DecodeSurname') IS NOT NULL
    DROP FUNCTION dbo.udf_DecodeSurname
GO

CREATE FUNCTION dbo.udf_DecodeSurname
(
	@Surname varchar(8000)
)
RETURNS varchar(8000) AS  
BEGIN

DECLARE @Result varchar(8000)
SELECT @Result = @Surname

SELECT @Result = REPLACE(@Result, '&#237;', 'i')
SELECT @Result = REPLACE(@Result, '&#225;', 'a')
SELECT @Result = REPLACE(@Result, '&#243;', 'o')
SELECT @Result = REPLACE(@Result, '&#241;', 'n')

return @Result
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abiel de GrootDeveloperAuthor Commented:
Doing it now.

A.
0
Abiel de GrootDeveloperAuthor Commented:
Hi All,

many thanks to all.

Both solutions seem to work fine and eradicate my initial problems with 'Order By' on Spanish Surnames. That is the solution provided by  BriCrowe and the initial solution posted by Angellll with the modifications suggested by BriCrowePosted.

The only this to note is that I have to call the UDF from 'Order By' not from the select statement.

Many thanks to all.
0
Brian CroweDatabase AdministratorCommented:
Use this one instead it automatically handles any of the unicode encoding..

IF OBJECT_ID('dbo.udf_DecodeSurname') IS NOT NULL
    DROP FUNCTION dbo.udf_DecodeSurname
GO

CREATE FUNCTION dbo.udf_DecodeSurname
(
	@Surname NVARCHAR(4000)
)
RETURNS NVARCHAR(4000) AS  
BEGIN

DECLARE @sStart INT,
	@sEnd INT,
	@UnicodeValue INT
	
SET @sStart = CHARINDEX('&#', @Surname)
WHILE @sStart > 0
BEGIN
	SET @sEnd = CHARINDEX(';', @Surname, @sStart + 2)
	SET @UnicodeValue = CAST(SUBSTRING(@Surname, @sStart + 2, @sEnd - (@sStart + 2)) AS INT)
	SET @Surname = STUFF(@Surname, @sStart, @sEnd - @sStart + 1, NCHAR(@UnicodeValue))
	
	SET @sStart = CHARINDEX('&#', @Surname)
END

RETURN @Surname
END

Open in new window

0
Abiel de GrootDeveloperAuthor Commented:
You function Works great in SQL2008R

However, the remote SQL is sql2000, where I can install the function, but it throws and error.

Don't worry the previous one wroked really well. I will have them upgrade their SQL.

Many thanks.



FYI - The error is:

Microsoft OLE DB Provider for SQL Server error '80040e14'

El parámetro de longitud pasado a la función de subcadena no es válido.
(The parameter of longtitude passes the function outside of a valid value.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.