?
Solved

Filter a view by a substring of a columns contents

Posted on 2010-11-08
10
Medium Priority
?
792 Views
Last Modified: 2012-05-10
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
Comment
Question by:dearness
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Accepted Solution

by:
dan_mason earned 252 total points
ID: 34089481
How about  WHERE RIGHT(ColName,2)='01' ? Alternative would be WHERE ColName LIKE '%01'
0
 
LVL 32

Assisted Solution

by:Erick37
Erick37 earned 248 total points
ID: 34089490
Ending in '01' can be selected like this:

where [column] like '%01'
0
 

Author Comment

by:dearness
ID: 34089588
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34089652
>>WHERE ('where Cook_ID' LIKE '%01')<<
I suspect this is what was intended (no points please):
WHERE Cook_ID LIKE '%01'
0
 

Author Comment

by:dearness
ID: 34089701
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
 

Author Comment

by:dearness
ID: 34089761
Thanks everyone,
 I managed to stumble on the answer based on all your suggestions

WHERE Cook_ID LIKE N'%01'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34089953
>>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
 

Author Comment

by:dearness
ID: 34090507
acperkins,
Useless unicode tag? and you are ranked as Genius.
It does not work without the useless unicode tag.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34095863
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34095870
On second thoughts.  Never mind, I would problably have to draw you a picture.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question