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/
No comments:
Post a Comment