AdventureWorks Database: Find the rows with green_ in the LargePhotoFileName column

Last update on December 20 2024 11:37:44 (UTC/GMT +8 hours)

161. From the following table wirte a query in SQL to search for rows with the 'green_' character in the LargePhotoFileName column. Return all columns.

Sample table: Production.ProductPhoto

productphotoid|thumbnailphoto                                                                                                                                                                                                                                                 |thumbnailphotofilename             |largephoto                                                                                                                                                                                                                                                     |largephotofilename                 |modifieddate           |
--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------------------+
             1|47494638396150003100F70000000000800000008000808000000080800080008080808080C0C0C0FF000000FF00FFFF000000FFFF00FF00FFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|no_image_available_small.gif       |474946383961F0009500F70000000000800000008000808000000080800080008080808080C0C0C0FF000000FF00FFFF000000FFFF00FF00FFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|no_image_available_large.gif       |2008-04-30 00:00:00.000|
            69|47494638396150003100F70000E3E3FCA6ACB3F5F6FE303D47F8FAFDEDEEFE989DA2F6F8FD6C86B5999B9DD3D3D5E2E5EACCD0D79AA2A8BDBEC2868B93ACB2B8BCBCBE5A6674526A913A4046F9FAFADBDCE9AAABAD656C76BFC0C7E9E9FDDCE1EA27272BEFEFFCDCDEE2C5CCE3CACBCCDADADC94A8C5B1B3B5FEFEFF5B76A86|racer02_black_f_small.gif          |474946383961F0009500F70000D3D3FEE2E3FE86878ADBDCFED7D8E7929BA8545659C9CAD8B7C4CD97999B030405767A8436424F8AAACCF9FAFA96A3AF719BCD7677793A3C40535963EBF3F8656569FAFBFDEBEBECC7C7E4BABCC5F7F9FCDBDCDE738CAEA8ADB327608BCCCCFDE2E3E4778591D2D3D5262629B5D0E25A636ED|racer02_black_f_large.gif          |2008-04-30 00:00:00.000|
            70|47494638396150003100F70000E3E3FCEBECF3F5F6FE999B9DEDEDFEDCE3EB374249B2BCCFABADB2F9FAFDD3D3D4373C44BBBCBEE2E5EA868B93F9FAFAACB0B6C9CBCCAAABADDCDDEADADADC5068907E848A26272AE9E9FD676D7598A2A9CDCFD7B1B3B5DBDDE1C5CCE3C2C3C5FEFEFEBEC0C194ACC7A2A3A65B75A60506066|racer02_black_small.gif            |474946383961F0009500F7000086878AECEDFED7D8E7D3D3FEE3E3FE909AA8DCDDFE545559C7C8D997999BB8C4CD96A2AF757679749ECE767A8289ABCAF9FAFA3A3C4038424C656569535962FAFBFDEDF0FBEBEBEDC8C8E6708FAEBABCC4DBDCDEF7F9FC77858F262629E2E3E4A9ADB25A636FCCCCFDD2D3D52E588ECACCCEB|racer02_black_large.gif            |2012-10-19 09:56:38.273|
            72|47494638396150003100F70000F1F2FEE3E3FCA3ACB6EBEBF3F5F6FEEDEEFE66676BF9FAFEE2E5EAB7BAC65656593B47539A9B9D989DA2D3D3D4787D835C6672D9E2E9DBDDE2868B93EFF0FDBBBCBEF9FAFAAAABAD3B3C42C8CBE6DBDCE9B6C8D5E9E9FDACBBC8D9DADD676C74CED0D37A858CFEFEFEC4D8E7748699206289E|racer02_blue_f_small.gif           |474946383961F0009500F700008EAFC785868991A5B2B5C7D1F2F3F4ECEDFE97989AD7D8E7D3D3FEE2E3FEDCDDFEC7C9D9757B8354555976777A76858FEBF2FAF9FAFA6991B2656669FAFBFD555A61EAEBED39414A3A3C40C7C7E6ACD3E8F7F9FCB9BCC5DBDCDEA8ADB2CCCCFDD2D3D5E2E3E477A9D150728E989CA2DDE1E3C|racer02_blue_f_large.gif           |2012-10-19 09:56:38.273|
			-- more --

Click to view Full table

Sample Solution:

-- Selecting all columns from the ProductPhoto table
SELECT *   
-- From the ProductPhoto table
FROM 
    Production.ProductPhoto  
-- Filtering records where LargePhotoFileName contains the pattern '%greena_%' using the ESCAPE clause to escape the character 'a'
WHERE 
    LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;

Explanation:

This SQL code retrieves all columns from the ProductPhoto table.
The SELECT statement specifies that all columns should be included in the result set.
The FROM clause indicates the table from which data is being retrieved, which is the ProductPhoto table.
The WHERE clause filters records to include only those where the LargePhotoFileName column contains the pattern '%greena_%'. The ESCAPE 'a' clause specifies that the character 'a' is used as the escape character to treat the underscore ('_') as a literal character rather than a wildcard.
The result set will contain all columns from the ProductPhoto table for records where the LargePhotoFileName matches the specified pattern.

Sample Output:

productphotoid|thumbnailphoto                                                                                                                                                                                                                                                 |thumbnailphotofilename             |largephoto                                                                                                                                                                                                                                                     |largephotofilename                 |modifieddate           |
--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------------------+
            76|47494638396150003100F700007689B3B1D5A475777BECEBF234353A85A376FAFCFE45474BF1F2FEE2E2FC6B919D4D5A6CF8FAFEF4F6FE65676BDAE7ECEDEDFE787C805B994CBEBDC3BACFB65A6674AAABB0F6F8FC56565A478C349A9DA1E3E4EBDCDDE1CDD1D9244356D1D1D3586F8EDBDDEA78CA6ABBBCBE68AB58F9FAFA3|racer02_green_f_small.gif          |474946383961F0009500F70000CACBF8D6E4EFA3D19EECEDFEF3F4F4D3D3FEE2E3FEDCDDFED7D7E7979AA6B4C7CEA5A7AAC7C8DA85868983B67633C90754555A2C930797999A719CCD96A5B0F9FAFA777A8287ABCEFAFBFDEEF0F876767977858A3B3C41656669575A604DAE2FC7C6ECEAEBED47CA233A4149BBBCC3F7F9FCA|racer02_green_f_large.gif          |2012-10-19 09:56:38.273|
            77|47494638396150003100F70000B8D3C4E2E3F397A5A945596B6B86B767AB56F5F8FC89BC7134353A45464AFAFCFEEBEBF3E1E2E3F1F2FEE3E3FB75767A797C816CC751F5F6FE65676BF8FAFD499434BEBDC26D9399EDEDFEA6AAB2ECF9E856565AE3E5E884D3713E47509A9DA15A9A4AADB2B6DEE3EADCDDE3CFD1D8D2D2D3D|racer02_green_small.gif            |474946383961F0009500F70000444549309407CACAF8D6E4EE34C90849CE24D8D8E8ECEDFEF2F3F3D3D3FEE2E3FEDCDDFE9699A7A4A6AAB9C4CD858689C6C8DA54555A719DCD96A5B098999A767A8350A731F9FAFAFAFBFD76767A88ABCDEEF1F4D4EDCF69A9563B3C41646569575A60EAEBED75868D6ADB49C6C6EC3C4148B|racer02_green_large.gif            |2012-10-19 09:56:38.273|
            91|47494638396150003100F7000000000000000800080800081008081008100808101008101810101810181810182118182118212118212921212921292921293129293131294229313129313931313939314A3139392152082B472E393942394242424A42425231426B395A6B4239394A39424A42424A424A4A4A4A4A524A4A4|julianax_r_02_f_green_small.gif    |474946383961F0009500F70000000205000810040C10101808081414101018101818101021101821181021181818181821182110211010291010261B1B182118102121182121212121292121182129212129182921182929212929292929212931292931213110213129214A18313121312129293131312D35294B0E293E202|julianax_r_02_f_green_large.gif    |2012-10-19 09:56:38.273|
            95|47494638396150003100F70000000000000008000808000810080810001000081010081018101018101818101821181821182121182129212129212929212931292931312939292942293131293139313139313939215A002D4E1C394A393139423939423942424A63424A6B4231294A39394A39424A42424A424A4A4A4A4A4|julianax_r_02_green_small.gif      |474946383961F0009500F70000000202000808000010000810020D0D081010000818081808081418101010101018101810101818180818181808211018181818211818101821181821082108212110182118212118102121212918213110294218264C10315218295A0C2E5D121821211821291829212118212121212121292|julianax_r_02_green_large.gif      |2012-10-19 09:56:38.273|
           105|47494638396150003100F700000200050D0512151012181818220E14211C252621262929293100002D292D3129313131313929313931313131393931392139103142210852001C5A08316B103939394231394239394242393939424239424242424A39424A42424A424A4A4A4A52424252424A524A4A5A4A4A6F10106D2E2E6|roadster_green_f_small.gif         |474946383961F0009500F7000000000000080008000000000800080808000C08080808081010020810081008100808101014100818081421080C2D040A1010101014141810101810181818181818212113162E1512182118182121252114271D2225272B2F252D1F490A20670144101144272B3131353F372B3931393939393|roadster_green_f_large.gif         |2012-10-19 09:56:38.273|
           106|47494638396150003100F70000020005080810100018120D1029040818151B211821231E262B1618292929312929292931312931313131313139392D31213910314221085200205A08316B103931393939394231394239393939424239424242424A39424A42424A424A4A4A4A632020573F41731C1C7B3C3C524A4A524A525|roadster_green_small.gif           |474946383961F0009500F7000000000000080008000000000800080808000C08080808081010020810081008100808101010100818081422080A3104081012121810101810181818182112161A1C1C211C1C2B191824241D2925292B272D3129311D51041C6300186B00296502451C2033312E35373045352D3F373D3C423C4|roadster_green_large.gif           |2012-10-19 09:56:38.273|
           123|47494638396150003100F7000010081018101821102921103118181821181821182121182921212129212929213131213929292929293131293129293939294A31313139313131313939313929314239393139393942393939394242394242394A4242464A42424A4E35577029314A634A31524A424A4A4A4A4E464E524A4E4|superlight_metalicgreen_f_small.gif|474946383961F0009500F70000150E111B1221221E262921292525312B273331292931293139392131312931313131393136293633333639393142393118314A312D4631424A3929423931394231393931423B3B3F4239424242394242424A3D4242394A42424A4A394A4A424A39473E29425C52424A5242524A4A4E524A4A3|superlight_metalicgreen_f_large.gif|2012-10-19 09:56:38.273|
           124|47494638396150003100F700001008101810181818182118182118212121212921292929292514312D2535292939312931332B3939313131313939313929314239393139393942393939394242394242394A4242424A6B2142424A4A424A4A4A4A4F474C524A52524A5A4A5A4A5D7B2652525263845252525A5A525A5A5A5A6|superlight_metalicgreen_small.gif  |474946383961F0009500F70000150E131C1821221D25292129291E3326292E3129293129312D2D3531312939293139293931313139313131313939462918314A21425A3929422E3C4A3931393939393F363C393F4C4239424A3942423F414444414A394A4A424A52424A524252414C3C394B504E4A4A4A524A525A39524A525|superlight_metalicgreen_large.gif  |2012-10-19 09:56:38.273|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Convert order date in the 'America/Denver' and 'America/Chicago' time zone.
Next: Get mailing addresses for companies in cities begin with PA outside US.