February 16, 2012 at 3:30 pm
Hi all, I have a decode statement in Oracle which takes the first character of the middle names to give the initials.
example forenames = John Charles then initials = C
forenames = John Charles David the initials = CD
I need to replicate this in SQL Server 2005. This is the statement in Oracle:
SELECT
forenames
,DECODE(INSTR(Per.forenames, ' ')
,0, NULL
,SUBSTR(Per.forenames
, INSTR(Per.forenames
,' '
,1
,1)
+ 1
,1))
|| DECODE(INSTR(Per.forenames
,' '
,1
,2)
,0, NULL
,SUBSTR(Per.forenames
, INSTR(Per.forenames
,' '
,1
,2)
+ 1
,1)) "INITIAL"
FROM person
Any ideas?
Many thanks
Jat
February 17, 2012 at 4:13 am
SQL Server doesn't have a DECODE function, but you can code your own:
CREATE FUNCTION dbo.DECODE(
@expression AS sql_variant,
@default sql_variant,
@search1 sql_variant, @result1 sql_variant,
@search2 sql_variant, @result2 sql_variant,
@search3 sql_variant, @result3 sql_variant,
@search4 sql_variant, @result4 sql_variant,
@search5 sql_variant, @result5 sql_variant,
@search6 sql_variant, @result6 sql_variant,
@search7 sql_variant, @result7 sql_variant,
@search8 sql_variant, @result8 sql_variant,
@search9 sql_variant, @result9 sql_variant,
@search10 sql_variant, @result10 sql_variant
)
RETURNS sql_variant
AS
BEGIN
RETURN
CASE
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
WHEN @expression = @search1 THEN @result1
ELSE @default
END
END
However, there's another challenge: INSTR maps to CHARINDEX in SQL Server, which doesn't accept the last parameter (nth_appeareance), so I guess you will have to change your strategy slightly.
Here's your sample data:
DECLARE @sampleData TABLE (
forenames nvarchar(50)
)
INSERT INTO @sampleData VALUES ('John Charles') -- initials = C
INSERT INTO @sampleData VALUES ('John Charles David') -- initials = CD
;
1st option: write a scalar function that parses the name
Ugly and procedural, but works quite well.
CREATE FUNCTION [dbo].[initials](@foreNames as nvarchar(4000))
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @result AS varchar(50)
DECLARE @iter AS int
DECLARE @prevchar AS char(1)
DECLARE @currchar AS char(1)
SET @iter = 1
SET @result = ''
WHILE @iter <= LEN(@foreNames)
BEGIN
SET @currchar = CONVERT(char(1),SUBSTRING(@foreNames,@iter,1))
IF (@prevchar = ' ' AND @currchar <> ' ')
BEGIN
SET @result = @result + @currchar
END
SET @prevchar = @currchar
SET @iter = @iter + 1
END
RETURN @result
END
GO
SELECT forenames, dbo.initials(forenames) AS initials
FROM @sampleData
2nd option: use the islands technique and identify name parts
Bulky and inefficient to some extent, but fun to code.
WITH
tenRows (N) AS (
SELECT 1 UNION ALL SELECT 2
UNION ALL
SELECT 3 UNION ALL SELECT 4
UNION ALL
SELECT 5 UNION ALL SELECT 6
UNION ALL
SELECT 7 UNION ALL SELECT 8
UNION ALL
SELECT 9 UNION ALL SELECT 10
),
-- ================================================
-- build a small 1000 rows tally table
-- ================================================
cteTally (N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM tenRows AS A
CROSS JOIN tenRows AS B
CROSS JOIN tenRows AS C
)
SELECT *
FROM @sampleData AS SD
CROSS APPLY (
SELECT INITIAL = (
SELECT C AS [text()]
FROM (
SELECT *,
posInGroup =
ROW_NUMBER() OVER (
PARTITION BY foreNames, delimitedGroupId
ORDER BY N
)
FROM (
SELECT *,
delimitedGroupId =
DENSE_RANK() OVER(
ORDER BY isDelimiter, _delimitedGroupId
)
FROM (
SELECT
N,
SUBSTRING(SD.foreNames, N, 1) AS C,
isDelimiter,
_delimitedGroupId =
ROW_NUMBER() OVER (
ORDER BY N
) -
ROW_NUMBER() OVER (
PARTITION BY isDelimiter
ORDER BY N
)
FROM cteTally AS TALLY
CROSS APPLY (
SELECT CASE WHEN SUBSTRING(SD.foreNames, N, 1) = ' ' THEN 1 ELSE 0 END
) AS Delim(isDelimiter)
WHERE N <= LEN(SD.foreNames)
) AS fs
) AS tk
WHERE delimitedGroupId > 1
AND isDelimiter = 0
) AS tk2
WHERE posInGroup = 1
FOR XML PATH('')
)
) AS CA
ORDER BY foreNames
3rd option: use a CLR function
It would be sensible. It's a complex scalar elaboration, so I guess it could perform well written in C#.
3rd option: use a split function
You could use the one described here[/url].
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply