Does anyone know SQL Server? : Othor Games

» Fri Sep 26, 2014 12:33 pm

This is probably basic, entry level stuff, but I've not learned it yet.

I'm using SQL Server Management Studio to try to find specific instances of people named Colby in a table containing customer info for my company. I can get a list of all customers for each store by entering the query:

select * from tb_customers where store_id in (1[or whatever the store id is])

There is a column named STORE_ID in the table, as well as a column named FIRST_NAME. However my ignorance is stopping me from finding all the Colbys. I followed the same sort of format and entered:

select * from tb_customers where first_name in (colby)

There was an error saying that there is no column named "colby." I'm sure this is a stupid question for people who know SQL, but what can I write to search for a specific name?

And can I search for something in FIRST_NAME and LAST_NAME at the same time?

» Fri Sep 26, 2014 12:04 am

Select from tb_customers where UPPER(first_name) LIKE UPPER('colby');

You can use the OR operator to select from multiple columns at the same time just add "OR UPPER(last_name) LIKE UPPER('colby')" to the end of the above to also search for colby in the last name field.

using "select *" is frowned upon and has poor performance. You never know when the database will change so best to select just the columns you want.

apparently sql server isn't case sensitive in the where clause, so the upper()s should be unnecessary.

» Fri Sep 26, 2014 7:22 am

Thanks Defron. I think you have answered me in every single tech question thread I've made. I should probably start paying you...

PS. That's super helpful info by the way.

» Fri Sep 26, 2014 9:32 am

If you want to also find records that might have extra characters (like spaces) in the field, you can use wildcards:

SELECT [columns] FROM [table] WHERE FIRST_NAME LIKE 'Colby%' to allow for extra characters at the end, or SELECT [columns] FROM [table] WHERE FIRST_NAME LIKE '%Colby' to allow for extra characters at the beginning, or SELECT [columns] FROM [table] WHERE FIRST_NAME LIKE '%Colby%' to find all records with 'colby' anywhere in the field. Just using LIKE 'colby' will exclude rows with any extra characters.

It depends on what you're doing. There are cases in which you want to make sure you're getting all columns and don't want to have to maintain a column list in every query when you add an applicable new column (this is often the case when implementing data access objects in a persistence layer of software). If you know that you want all of the columns for a particular query, using a column list is just adding maintenance time when things change...IMO it's better in some specific cases to split the difference and use SELECT * (or [table].* in a query with joins). SELECT * can be evil, but it's not always evil. :wink:

It depends on the configuration of the SQL Server instance, or even the collation settings for a particular table or query. In most cases, the default settings will not enforce case-sensitivity. You can also use the COLLATE keyword to change collation settings for a specific SQL statement.

OP, for what it's worth, none of this (aside from the aforementioned COLLATE stuff) is specific to SQL Server since you're not using any functionality specific to TRANSACT (SQL Server's default language in statements). It's just part of the SQL standard that most database engines use.