Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 801
  • Last Modified:

Filter a view by a substring of a columns contents

I am using SQL server 2005 and need to create a view based on a substring of a columns contents.
The column contents are strings in the form 101125122101 , I want to display the view with only the strings ending in 01 as in the example.

I have made a guess at column filter =N'??????????01' but that didn't work
0
dearness
Asked:
dearness
2 Solutions
 
dan_masonCommented:
How about  WHERE RIGHT(ColName,2)='01' ? Alternative would be WHERE ColName LIKE '%01'
0
 
Erick37Commented:
Ending in '01' can be selected like this:

where [column] like '%01'
0
 
dearnessAuthor Commented:
Dan and Erick,
Thanks for the prompt reply, however both suggestions do not filter the column variables ending 01, I had no items returned in the view

My original SQL statement was
SELECT Cook_ID,Start_Time,Finish_Time,Finish_BBone
FROM dbo.Precooker_Log

Both your suggestions added this to the bottom of the statement
WHERE ('where Cook_ID' LIKE '%01')
 
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
>>WHERE ('where Cook_ID' LIKE '%01')<<
I suspect this is what was intended (no points please):
WHERE Cook_ID LIKE '%01'
0
 
dearnessAuthor Commented:
acperkins,
Thanks for the reply but exactly the same result, no views from
 
SELECT Cook_ID,Start_Time,Finish_Time,Finish_BBone
FROM dbo.Precooker_Log
WHERE ('where Cook_ID' LIKE '%01')

I get views of all data without the WHERE clause.
0
 
dearnessAuthor Commented:
Thanks everyone,
 I managed to stumble on the answer based on all your suggestions

WHERE Cook_ID LIKE N'%01'
0
 
Anthony PerkinsCommented:
>>I managed to stumble on the answer based on all your suggestions<<
Grief! Did you want me to go over there and type it for you?  Did you not see what I posted?  
This is what I posted here http:#a34089652:
WHERE Cook_ID LIKE '%01'

And this is what you posted here http:#a34089761:
WHERE Cook_ID LIKE N'%01'

You have to admit aside from the useless unicode flag there is a remarkable similarity. :)
0
 
dearnessAuthor Commented:
acperkins,
Useless unicode tag? and you are ranked as Genius.
It does not work without the useless unicode tag.
0
 
Anthony PerkinsCommented:
>>does not work <<
I am afraid that comment is meaningless in my dictionary.  If you would like to elaborate what that means, I would be happy to clarify.  In other words did you:
A. Get an error message?  If so, what was it?
B. Did you get the wrong results?  If so, what were they and what were the expected results.
C. Something else.
0
 
Anthony PerkinsCommented:
On second thoughts.  Never mind, I would problably have to draw you a picture.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now