SQL Server – String Comparison Without CASE Statements

casesql serverstring

Is there a shorter more terse way to do a string comparison in SQL Server without wrapping it in a CASE WHEN ... THEN 1 END? In PostgreSQL, I can do

SELECT c, c='foo'
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In SQL Server, I have to do

SELECT c, CASE WHEN c='foo' THEN 1 END
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In the abstract, is there an easier more SQL-Servery way to throw an expression into boolean context? Or, is there a better way to write strcmp without rolling your own case statement?

Best Answer

I'm not sure that you'd call it shorter, but you could use the Logical Function IIF. From the docs,

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation.

Per the example,

SELECT c, iif(c='foo',1,0)
FROM ( VALUES ('foo'),('bar') ) AS t(c);

Related Solutions

Sql-server – Calling a .NET method in a SQL Server script

It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script.

CLR Assembly:

Must be installed into a database (both the assembly binary and the exposed wrapper method(s)).
Requires setting up an external project in Visual Studio, something you may not be very familiar with.
Very easy to integrate into your existing script.
Debugging can be a bit involved, because you have to either debug locally (i.e., on the server), set up remote debugging, or set up a comprehensive test suite. All of these can be problematic for different reasons. (Note: setting up a test suite is a good idea, but if time is a factor and you've already received a tested chunk of code, it's much safer to skip it in this scenario than if you converted the code to T-SQL, for example.)

SSIS Script Component:

Means running a totally separate process to do the calculation.
Much easier to get started than with a separate assembly, where you have to provide all the boilerplate code. With SSIS, pretty much all you do is fill in the required method bodies.
BIDS, which is x86, can't debug x64 scripts, so there's some ridiculousness that comes from that. (See here for a quick fix.) This would allow you to debug locally (on your workstation), though, which is an advantage.

While I haven't tested, I'll go out on a limb and say that the performance of both methods should be similar. If performance is a big concern, set up both scenarios and comparison test. In fact, you may want to set up both anyway just for practice, so you can see some of the differences and similarities for yourself, which is way more interesting and fun than reading about it (well, at least I think so).

A third option would be to move the process entirely to SSIS, which is more suited to migration/ETL types of tasks. This may or may not be feasible. You wouldn't necessarily need to retool the whole process, as you can, of course, execute arbitrary scripts.

Finally, another option is to convert the code to T-SQL. Depending on the complexity, though, it may be quite a challenge. Certainly there are manipulation tasks much better suited to using a proper programming language/framework like .NET. Without seeing the code itself, it's impossible to say if it would even be worth an attempt. As I mentioned previously, you would definitely need to develop a comprehensive test suite in this case.

Personally, I would probably create a CLR assembly because I'm strong on the programming side of things and it would be the fastest, least intrusive way to integrate the code. But that's me, and I haven't seen the code, so it may not be the best solution for you and your environment.

Sql-server – Update “NULL” string to Actual NULL value

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

Best Answer

Related Solutions

Sql-server – Calling a .NET method in a SQL Server script

Sql-server – Update “NULL” string to Actual NULL value

Related Question