I have an user-defined function in T-SQL which uses CHARINDEX() internally. I take @caseSensitivity(bit) parameter as an input, which should decide the casing(collation) of the CHARINDEX() function. Is there a way to do that without making two if-constructs for every occurence of CHARINDEX()?
Asked
Active
Viewed 78 times
0
-
1Can you post a code sample? – Jace Sep 05 '14 at 16:58
-
Have you considered `CASE` instead of `IF`. Depends on your logic. – Tak Sep 05 '14 at 18:20
-
@t_m, using case wont do much good either because it will produce the same quantity and quality of code. – zhulien Sep 09 '14 at 08:29
1 Answers
1
Unfortunately, there isn't a way to set a connection-level collation override, if that is what you were thinking (I was).
However, if you are just looking for a way to be able to control this easier, then you may be able to evaluate all the CHARINDEX lookups as case-sensitive and then use UPPER to make it case-insensitive when it @caseSensitive is zero:
DECLARE @caseSensitivity BIT = 1
DECLARE @input VARCHAR(3) = 'Abc'
DECLARE @inputCheck VARCHAR(3) = 'B'
IF @caseSensitivity = 0
BEGIN
SET @input = UPPER(@input)
SET @inputCheck = UPPER(@input)
END
SELECT CHARINDEX(@inputCheck COLLATE SQL_Latin1_General_CP1_CS_AS, @input COLLATE SQL_Latin1_General_CP1_CS_AS, 0)
Community
- 1
- 1
Jordan Parker
- 1,208
- 1
- 16
- 25
-
This is actually a great solution for the problem. I was looking just for something like that. Thank you! – zhulien Sep 09 '14 at 08:21