SQL Server - CASE FN(str) WHEN x THEN y ELSE keep original value without repeating the functions
Clash Royale CLAN TAG#URR8PPP
SQL Server - CASE FN(str) WHEN x THEN y ELSE keep original value without repeating the functions
I'm wondering whether a variable exists within a case statement that you can use to refer back to the value that was passed in.
For instance, if you have a table like this:
Department
shipping
receiving
hr
And want this output:
Shipping
Receiving
HR
Here's a query that does it:
DECLARE @table TABLE (dept VARCHAR(25))
INSERT INTO @table VALUES ('shipping'), ('receiving'), ('hr')
SELECT
CASE UPPER(SUBSTRING(dept, 1, 1)) + LOWER(SUBSTRING(dept, 2, LEN(dept) - 1))
WHEN 'Hr' THEN 'HR'
ELSE UPPER(SUBSTRING(dept, 1, 1)) + LOWER(SUBSTRING(dept, 2, LEN(dept) - 1))
END
FROM @table
Is there a way to do it without repeating all the functions in the else statement? In the ELSE, I just want it to return the original value I passed into the case statement. I'm looking for some built-in variable for the original value, conceptually like this:
ELSE ORIGINAL_VALUE
To be clear, the point of the question is to find out whether a specific variable exists in a CASE statement to reference the original value. I'm not literally trying to accomplish this casing outcome. It's just test data to represent something analogous.
– Max Szczurek
Aug 8 at 17:53
Simply or obscure too much often means no one really understands your goal. Terminology matters as well. The only variable in your script is the table. In addition, CASE is not a statement but an expression. It is unclear what "without repeating all the functions ..." actually means and the responses (and your own query) do exactly what you ask. If that is not useful, then obviously you need to provide more and better information. Perhaps by "variable" you mean the value of the expression in your first branch. A CTE or a derived table could be used.
– SMor
Aug 9 at 15:20
And I stand by the first suggestions - use a view or a computed column. If this repetition is so taxing, then solve the problem once and never write this same code anywhere else.
– SMor
Aug 9 at 15:22
@SMor Thanks for your insight!
– Max Szczurek
Aug 9 at 15:46
1 Answer
1
You can use apply with values :
apply
values
SELECT t.*, (CASE WHEN ORIGINAL_VALUE = 'Hr' THEN 'HR' ELSE ORIGINAL_VALUE
END)
FROM @table t CROSS APPLY
( VALUES (UPPER(SUBSTRING(dept, 1, 1)) + LOWER(SUBSTRING(dept, 2, LEN(dept) - 1)))
) tt(ORIGINAL_VALUE);
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
It would, of course, be FAR better to alter the actual values once and then add the appropriate logic to prevent this sort of thing in the future. But that is surely shouting at the wind, so why not create either a view that does this or add a computed column? And simplify your life. Your first case branch can just be " when upper(dept) = 'HR' then 'Hr' else ..." You don't need to pull apart the 2 characters and then upper/lower each one and then string them back together.
– SMor
Aug 8 at 17:44