SQL where LEFT query 1

[RosieGp]

Hi there,
I have data as follows:
ID Name Track_type
578-1 Sugar Kitchen - Sugar001
578-1 Salt Snow - Salt002
57869-1 Vitamin A Health - Vit001
57869-2 Vitamin C Health - Cal001
5786900-1 Tea Tree - Tea001
5786900-2 Coffee Tree - Coffee002
488043-1 Yogurt Kids - Yog005

Now i just need to display records based on the ID numbers before '-'
so if the user want to see what is under ID 578 it should only display:

578-1 Sugar Kitchen - Sugar001
578-1 Salt Snow - Salt002

or Under 57869 it should show only the following:

57869-1 Vitamin A Health - Vit001
57869-2 Vitamin C Health - Cal001

or Under 5786900 it should show only the following:

5786900-1 Tea Tree - Tea001
5786900-2 Coffee Tree - Coffee002

The Sql Statement that I have is as following:

@UserChoice varchar(10)

select ID, Name, Track_type
from Ingredients
where (left (ID, 5) = left (@UserChoice, 5))

this one shows the following:
57869-1 Vitamin A Health - Vit001
57869-2 Vitamin C Health - Cal001
5786900-1 Tea Tree - Tea001
5786900-2 Coffee Tree - Coffee002

how to modify this query to accomodate above results...

Any help is appreciated....

 

[simian336]

The dash is easy... the zeros are a little more problematic.

SUBSTRING (ID ,1 , CHARINDEX(' - ', ID)-1 ))

Simi

 

[simian336]

oops copied some old code...

Try this...

select SUBSTRING(@id ,1 , CHARINDEX('-', @id)-1 )

Simi

 

[RosieGp]

Sorry if I was not clear...
I donot have to worry about the dash '-' as the user thru the front end is only going to enter integers before the dash.
My question is how to compare the values in the where clause so that to show only the related values...
for example if the user enters 57869.
it only shows 2 records:
57869-1 Vitamin A Health - Vit001
57869-2 Vitamin C Health - Cal001

or 5786900 it should show only the following:

5786900-1 Tea Tree - Tea001
5786900-2 Coffee Tree - Coffee002

Right now with my query
select ID, Name, Track_type
from Ingredients
where (left (ID, 5) = left (@UserChoice, 5))
it shows the following:

57869-1 Vitamin A Health - Vit001
57869-2 Vitamin C Health - Cal001
5786900-1 Tea Tree - Tea001
5786900-2 Coffee Tree - Coffee002

 

[PWise]

how about

select  ID, Name, Track_type
from Ingredients
where left (ID, len(@UserChoice)) = left (@UserChoice, len(@UserChoice))

 

[markros]

If you split your column (you may create a computed column in the table) to only get the portion before -, then your condition will become

where IDWithoutDashes = @UserChoice

PluralSight Learning Library

 

[RosieGp]

PWise,
I used that query but the results are same, if the user enters 578 it displays all the records satrting with 578.
Thanks.

markros,
IT won't do it. I have to work with what i get.

I was wondering if i can use substring or trim functions for this.
Any help is appreciated.

Thanks...

 

[gmmastros]

Embrace the dash! Don't try to get rid of it, instead, try to use it. Ex:

Select	* 
From	YourTableNameHere
Where	id like @UserChoice + '[-]%'

Guess what.... it's sargable too.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RosieGp]

gmmastros,
thanks for the reply. trust me '-' is not my problem as thru the front end i just grab numbers before '-'.
In SQL, data is like this:
123-1
123-2
45678-2
45678-3
456-1
456-5 etc.

The issue that i have is how to modify the follwoing query:
select ID, Name, Track_type
from Ingredients
where left (ID, len(@UserChoice)) = left (@UserChoice, len(@UserChoice))

so that if the user enters 456 it only returns 2 rows:

456-1 Tea Tree - Tea001
456-5 Coffee Tree - Coffee002

right now it returns all four:

456-1 Tea Tree - Tea001
456-5 Coffee Tree - Coffee002
45678-2 Juice Apple
45678-3 Punch Stawberry

 

[gmmastros]

Rosie, I am pretty sure I correctly understood the problem, but I may not have correctly explained my solution. Let me try again.

Let's assume the following data.

456-1
456-5
45678-2
45678-3

Now, let's assume that the user is looking for 456, so @UserChoice = '456'. If we attempt to construct a query that looks for 456 in the left 3 positions, all rows (in the example data) are returned. If we look for [!]456-[/!] in the left [!]4[/!] positions, we will return only the 2 rows that we actually want.

Now... we could write a query that uses a lot of functions in the where clause, but this is likely to be slower than if we use a like comparison because the query is now [google]SQL Server Sargable[/google]. If you are not sure what that means, please look it up.

Anyway.... please run the following code in a query window and let me know if it generates the correct results.

Declare @UserChoice VarChar(10)
Set @UserChoice = '456'

Select 'Looking for ' + @UserChoice

select ID, Name, Track_type
from   Ingredients
where  ID Like @UserChoice + '[-]%'

Set @UserChoice = '45678'
Select 'Looking for ' + @UserChoice

select ID, Name, Track_type
from   Ingredients
where  ID Like @UserChoice + '[-]%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

To further illustrate my point, I created a table variable to store your sample data. You can copy/paste this code to a query window to see how it works.

Declare @Temp Table(Id VarChar(20), Name VarChar(20), Track_Type VarChar(100))

Insert Into @Temp Values('578-1'     ,'Sugar'       ,'Kitchen - Sugar001')
Insert Into @Temp Values('578-1'     ,'Salt'        ,'Snow - Salt002')
Insert Into @Temp Values('57869-1'   ,'Vitamin A'   ,'Health - Vit001')
Insert Into @Temp Values('57869-2'   ,'Vitamin C'   ,'Health - Cal001')
Insert Into @Temp Values('5786900-1' ,'Tea'         ,'Tree - Tea001')
Insert Into @Temp Values('5786900-2' ,'Coffee'      ,'Tree - Coffee002')
Insert Into @Temp Values('488043-1'  ,'Yogurt'      ,'Kids - Yog005')

Declare @UserChoice VarChar(10)

Set @UserChoice = '[!]578[/!]'

Select	ID, Name, Track_Type
From	@Temp
Where	id like @UserChoice + '[-]%'

After running the code, change the @UserChoice value to '57869' and re-run it. You should notice that it returns the correct results. If it doesn't, please let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RosieGp]

OMG!!! you are genius.
It works perfectly,
sorry for misundersatnding before but after running your code step by step, i understood.
Thanks.


Thanks to everyone who replied.
I really appreciate.