Need to use MID function in SQL

When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following?

CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END.

I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash.

Thanks.

Ernie

You have to combine sql sever string function

like "left" and "right" to achive you requirements

I think the equivalent of vb mid function is the "substring" function

This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.

USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname 

|||

create table #test (SearchID varchar(49))
insert into #test values('aaa-bbbbb')
insert into #test values('aaaa-bbbbb')

one way using ParseName
Select ParseName(Replace(SearchID , '-', '.'), 2)
from #test

and another using left and charindex
select distinct LEFT(SearchID ,CHARINDEX('-',SearchID )-1 )
from #test

and a third using case substring and left
select CASE substring(SearchID , 4 , 1) when '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END
from #test

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks! I appreciate the help.