Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Substring / Distinct?

Author  Topic 

Crima
Starting Member

17 Posts

Posted - 2012-11-14 : 23:48:31
Hi I am trying to figure out this problem and it has me stumped. That last part
"Where the first letter of the vendor name is N, O or P" Has me confused.
I thought of using SELECT DISTINCT or using a Substring, can I get some insight?

Problem:
Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns
three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT
statement that returns all of the columns in the view, sorted by VendorName, where the
first letter of the vendor name is N, O, or P.

My Code:

CREATE VIEW InvoiceBasic
--RETURN VendorName, InvoiceNumber, InvoiceTotal
SELECT VendorName, InvoiceNumber, InvoiceTotal
WHERE SUBSTRING(N, O, P)
ORDER BY VendorName


A witty student

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-11-15 : 03:33:45
Look up the syntax of substring (http://msdn.microsoft.com/en-us/library/ms187748.aspx). The Start and the length parameters of the function are in this case both 1. Then look up IN (http://msdn.microsoft.com/en-us/library/ms177682.aspx). Here's a hint for the final syntax: The substring of vendorname is in you collection of letters and each letter should be surrounded by single quotes.

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 05:23:17
you could just use

WHERE VendorName LIKE '[NOP]%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -