Solved

Filter a view by a substring of a columns contents

Posted on 2010-11-08
10
788 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 63 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 62 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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