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
Solved

Filter a view by a substring of a columns contents

Posted on 2010-11-08
10
785 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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