Filter

[gsavitha]

Dear All,

I need to filter traveller by age.

How to filter this for eg:

Traveller (column)
Mrs.Dolo Jaku, 72; Mr.Jame Jaku, 65;
Mr.Kate blitz, 22; Ms.sheva dove, 36;
Mr.James don, 22; Ms.Fleming don, 36; Mr.James Jaku, 65

Result

Mrs.Dolo Jaku, 72; Mr.Jame Jaku, 65;

I need to filter all traveller whose age is greater than 65

Any help would be highly appreciated.Many Thanks

Reagrds,
SG

 

[r937]

this is really easy

WHERE age >= 65

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Qik3Coder]

r937,
I believe, if I understand his problem, it's a little more difficult than that.

gsavitha,
I'm going to give you most of the solution, and it up to you to connect the dots.

1. You will need this User Defined funtion

http://www.logiclabz.com/sql-server...break-comma-separated-strings-into-table.aspx

2. Here is how you use that function to separate the fields

Select * from dbo.Split('Mr.James don, 22;Ms.Fleming don, 36;Mr.James Jaku, 65', ';')

3.Once you have the values split here is the code to separate the age

Declare @_tbl as Table(
id int identity(1,1),
col1 varchar(2000))

Insert into @_tbl
SELECT 'Mrs.Dolo Jaku, 72;Mr.Jame Jaku, 65' UNION ALL
SELECT 'Mr.Kate blitz, 22;Ms.sheva dove, 36' UNION ALL
SELECT 'Mr.James don, 22;Ms.Fleming don, 36;Mr.James Jaku, 65'

Select col1, Age FROM
(
	Select col1, reverse(col1) Flip, charindex(',', reverse(col1)) Cut,
	cast(reverse(substring(reverse(col1), 0, charindex(',', reverse(col1)))) as int) Age
	from @_tbl) as a
WHERE a.Age >= 65

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[r937]

nice one, lod

you realize i was just stringin the guy along, right?

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Qik3Coder]

Oh, I know.
I've been in a mood lately...wanting people to post the code that have, that they can't get to work.

I almost missed that the columns were strung together.
I had "step 3" all done and was like hrmmm.



I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[r937]

my reason for answering with

WHERE age >= 65

was to get him to reply with "but i don't gots an age column"

at which point i would say "aha! there's your problem, isn't it"



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

by the way, two things you should know about gsavitha --

he starts a thread, but never comes back to acknowledge any replies or answer any questions for more information

he routinely posts the same questions in other forum sites, and never comes back to those, either

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Qik3Coder]

The lack of the age column is bad enough, but it looks like this is the output of some other query.

The fact that name-age pairs are strung together is the real nasty point.


gsavitha,
None of this is intentionally bashing your post.
Rather it is highlighting the aspects that make this an interesting problem.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[Qik3Coder]

hrm,
10 threads started -> 1 reply

I wonder if he has my problem.
I post a question that I'm having and 10 seconds after posting, I find the solution.


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD