Solved

Filter a view by a substring of a columns contents

Posted on 2010-11-08
10
784 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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