SUBSTRING_INDEX' error

Question

This code is executing fine in MySQL :

SELECT SUBSTRING_INDEX(empid, '-', 1) 
FROM employees

But it throws this error in SQL Server :

'SUBSTRING_INDEX' is not a recognized built-in function name.

I don't want to use where clause.

The error is fairly self explanatory, `SUBSTRING_INDEX` is not a built in function in SQL Server. What is your question? — GarethD, Jan 16 '15 at 11:14
For this particular example you can use something like: `SELECT LEFT(empid, CHARINDEX('-', empid, 1) - 1) FROM employees` - This is not quite comparable to `SUBSTRING_INDEX` (hence a comment not an answer), but it should work for your case. — GarethD, Jan 16 '15 at 11:22
i am getting following error : Invalid length parameter passed to the LEFT or SUBSTRING function. — abhishek, Jan 16 '15 at 11:28
now getting following error :Invalid length parameter passed to the LEFT or SUBSTRING function. — abhishek, Jan 16 '15 at 11:43
`SELECT LEFT(empid, ISNULL(NULLIF(CHARINDEX('-', empid, 1) - 1, -1), LEN(empID))) FROM employees` — GarethD, Jan 16 '15 at 11:50

score 3 · Answer 1 · answered Jan 16 '15 at 11:43

3

In SQL Server, you can do:

select (case when empid like '%-%'
             then left(empid, charindex('-', empid) - 1)
             else empid
        end)
from employees;

answered Jan 16 '15 at 11:43

Gordon Linoff

1 Answers1