Decode( ) Function in SQL Server

DECODE( ) function in SQL Server

You could use the 'CASE .. WHEN .. THEN .. ELSE .. END' syntax in SQL.

Convert Oracle decode statement into SQL Server T-SQL statement

Could you try a CASE statement instead?

Dim query As String = "
SELECT
docType, 
docyear, 
docmonth, 
TO_CHAR(TO_DATE(docmonth||'/01/2008', 'MM/DD/YYYY'), 'docmonth')) 
CASE
  WHEN docmonth IS NULL THEN NULL
  WHEN docmonth = '0' THEN '- All Months -'
  WHEN docmonth >= AND docmonth <= 12 THEN DATENAME(month, DATEADD(month, docmonth, -1))
END CaseDocMonth,
monthAlpha, 
DID 
FROM dbo.Document
WHERE XALASKAID = '" & LicenseNumber & "'
AND DOCTYPE like '%Report%';"

I've written about DECODE before, and CASE is generally preferred anyway, because it's easier to read, and has more flexibility.

Convert Decode from Oracle to Case from MS SQL Server

The decode formula is equivalent to

case sum(case when a.canceldate is null then 1 else 0 end) when 1 then null
     else to_date( ... )    end dCancelDate, ...

One mistake I saw in your translation is that you have when sum(...) when 1. You can't have it both ways, it is either when sum(...) = 1 or sum(...) when 1. It may be the only mistake, I didn't look too hard.

What you have within the to_date() is horrible; are you converting dates to character strings, then take the max IN ALPHABETICAL ORDER and then translate back to date? Why? Perhaps just so you delete the time-of-day component? That is a lot easier done with trunc(max(a.canceldate)).

Is there any way to do HTML decode in SQL Server?

The following SQL function would work in your case or it would be a good starting point for you to extend it. However, please note the String manipulations in the Database [SQL Server] would be slower compared to the string manipulations in application layer.

GO

IF OBJECT_ID('dbo.MyHTMLDecode') IS NOT NULL BEGIN DROP FUNCTION dbo.MyHTMLDecode END

GO
CREATE FUNCTION dbo.MyHTMLDecode (@vcWhat VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @vcResult VARCHAR(MAX)
    DECLARE @siPos INT
        ,@vcEncoded VARCHAR(7)
        ,@siChar INT

    SET @vcResult = RTRIM(LTRIM(CAST(REPLACE(@vcWhat COLLATE Latin1_General_BIN, CHAR(0), '') AS VARCHAR(MAX))))

    SELECT @vcResult = REPLACE(REPLACE(@vcResult, ' ', ' '), ' ', ' ')

    IF @vcResult = ''
        RETURN @vcResult

    SELECT @siPos = PATINDEX('%&#[0-9][0-9][0-9];%', @vcResult)

    WHILE @siPos > 0
    BEGIN
        SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 6)
            ,@siChar = CAST(SUBSTRING(@vcEncoded, 3, 3) AS INT)
            ,@vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
            ,@siPos = PATINDEX('%&#[0-9][0-9][0-9];%', @vcResult)
    END

    SELECT @siPos = PATINDEX('%&#[0-9][0-9][0-9][0-9];%', @vcResult)

    WHILE @siPos > 0
    BEGIN
        SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 7)
            ,@siChar = CAST(SUBSTRING(@vcEncoded, 3, 4) AS INT)
            ,@vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
            ,@siPos = PATINDEX('%&#[0-9][0-9][0-9][0-9];%', @vcResult)
    END

    SELECT @siPos = PATINDEX('%#[0-9][0-9][0-9][0-9]%', @vcResult)

    WHILE @siPos > 0
    BEGIN
        SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 5)
            ,@vcResult = REPLACE(@vcResult, @vcEncoded, '')
            ,@siPos = PATINDEX('%#[0-9][0-9][0-9][0-9]%', @vcResult)
    END

    SELECT @vcResult = REPLACE(REPLACE(@vcResult, NCHAR(160), ' '), CHAR(160), ' ')

    SELECT @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult, '&', '&'), '"', '"'), '<', '<'), '>', '>'), '&amp;', '&')

    RETURN @vcResult
END

GO

Illustration:

  DECLARE @S VARCHAR(MAX)='CD&M Communications 
    auburndale oil & propane inc  
    C F La Fountaine #7561  
    Laramie County Fire District # 2  
    AmeriGas Propane LP #2250'

    SELECT dbo.MyHTMLDecode (@s)

OUTPUT:

CD&M Communications 
auburndale oil & propane inc  
C F La Fountaine   
Laramie County Fire District # 2  
AmeriGas Propane LP 

---

Related Topics