Reputation: 277

how to use substr in SQL Server?

I have the following extract of a code used in SAS and wanted to write it in SQL Server to extract data.

substr(zipname,1,4) in("2000","9000","3000","1000");run;

How do I write this in SQL Server ?

I tried and got this error:

An expression of non-boolean type specified in a context where a condition is expected

Upvotes: 2

Answers (3)

Reputation: 20794

Since you want the first x characters, you can also use the left() function.

 where left(zipname,  4) in (values go here)

Bear in mind that your values have to be single quoted. Your question has double quotes.

Upvotes: 1

Reputation: 1569

You need a table that you are getting the records from, and zipname would be a column in the table. The statement would be something like this:

select * from tablename where substring(zipname,1,4) in ('2000','9000','3000','1000')

Upvotes: 1

Reputation: 60493

In sql server, there's no substr function (it's substring)

by the way, you need a complete query...

select blabla
from blibli
where substring(zipname, 1, 4) in ('2000', '9000', 3000', '1000')

assuming zipname is a varchar or something like that...

Upvotes: 5