2

I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using

reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) )

This works great for most of my records, however some are failing due to charindex returning 0 while a '.' is definitely part of the string.

I think I've isolated it to an issue with accented characters and a few others.

One of them I have is ité.pdf, when reversed, charindex is not finding the '.'

fdp.́eti

I've taken to converting it to it's hex representation to narrow down what's going on using

cast(reverse(filename) as varbinary(max))

0x6600640070002E000103650074006900

From what I can work out The '.' is 2E 00. 01 03 65 00 is the 'é' which is encoded differently than the others I have in the same column.

Since 2E 00 comes first I would still expect charindex to get a match, but it's returning me 0, which in turn make the call to LEFT fail.

Am I missing something obvious here? Converted to HEX it seems the '.' I am looking for is definitely there, but the character after is tripping charindex somehow.

EDIT: Simplified test

When outputting the string there is clearly a ., but charindex is not finding it?

select charindex('.', cast(0x6600640070002E000103650074006900 as nvarchar(max)))

Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cda6f0a77fa9e206bff9217ec58be075

  • which version of sql server are you running? – kevinnwhat Nov 1 '19 at 20:44
  • @kevinnwhat 2016 – jfrobishow Nov 1 '19 at 20:59
  • @McNets - dbfiddle.uk/… – jfrobishow Nov 1 '19 at 21:25
  • 1
    You could try by casting it as varchar(255). dbfiddle.uk/… – McNets Nov 1 '19 at 21:41
  • casting it to varchar works, I'll use that if you want to post it as an answer. I wouldn't mind knowing what's going on with the nvarchar if someone has any idea. – jfrobishow Nov 1 '19 at 22:11
1

Apparently it seems related to a collation or encoding issue.

I'd check database and column collations to ensure both are the same.

Server collation

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

Database collation

SELECT 
    name, 
    collation_name 
FROM 
    sys.databases
WHERE 
    name = 'DBName';

Column collation

SELECT 
    name, 
    collation_name 
FROM 
    sys.columns 
WHERE 
    name = N'ColumnName';

Quoted from MS-Docs Collation precedende

Functions and Collation

THE CAST, CONVERT, and COLLATE functions are collation sensitive for char, varchar, and text data types. If the input and output of the CAST and CONVERT functions are character strings, the output string has the collation label of the input string. If the input is not a character string, the output string is Coercible-default and assigned the collation of the current database for the connection, or the database that contains the user-defined function, stored procedure, or trigger in which the CAST or CONVERT is referenced.

For the built-in functions that return a string but do not take a string input, the result string is Coercible-default and is assigned either the collation of the current database, or the collation of the database that contains the user-defined function, stored procedure, or trigger in which the function is referenced.

The following functions are collation-sensitive and their output strings have the collation label of the input string:

CHARINDEX, REPLACE, DIFFERENCE, REVERSE, ISNUMERIC, RIGHT, LEFT, SOUNDEX, LEN, STUFF, LOWER, SUBSTRING, PATINDEX, UPPER

Let me suggest to have a look at this or this answers of Solomon Rutzky about UTF-8 and UTF-16 encoding values for VARCHAR and NVARCHAR.

By now you can try by CASTING it to VARCHAR(255) before use CHARINDEX.

I've tried to reproduce it in one of my servers in this way:

DECLARE @t VARBINARY(MAX) = 0x6600640070002E000103650074006900;
DECLARE @vt VARCHAR(MAX);

SELECT CAST(@t AS nvarchar(MAX));

---------
fdp.́eti


SET @vt =  CAST(CAST(@t AS nvarchar(MAX)) AS VARCHAR(MAX))

SELECT REVERSE(LEFT(REVERSE(@vt), CHARINDEX(N'.', REVERSE(@vt))-1));

-------
´eti

Note it returns the accent as a single character.

| improve this answer | |
0

You can extract extension without using reverse function:

declare @x nvarchar(max) = reverse(cast(cast(0x6600640070002E000103650074006900 as varbinary(255)) as nvarchar(255))) collate LATIN1_GENERAL_100_CI_AS_SC;

select substring(@x, charindex('.', @x) +1 , LEN(@x) - charindex('.', @x))

If you have multiple dots, then you may force TSQL to use binary collation or cast to varchar:

select reverse( left( reverse(@x collate LATIN1_GENERAL_100_BIN), charindex('.', reverse(@x collate LATIN1_GENERAL_100_BIN) ) -1 ) )

select reverse( left( reverse(cast(@x AS varchar(255))), charindex('.', reverse(cast(@x AS varchar(255)) )) -1 ) )
| improve this answer | |
  • That won't work if there's multiple dots in the filename though, that's why I was using reverse. – jfrobishow Nov 1 '19 at 21:14
  • select charindex('.', cast(0x6600640070002E000103650074006900 as nvarchar(max))) – jfrobishow Nov 1 '19 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.