Click here to Skip to main content
15,944,733 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Following is the sql query:


select  SKU,PRODUCTNAME,PRODUCTVARIANT from tbl_product where SKU  in(SELECT * FROM splitstring('ewew,slk,ewew'))



I need to get 3 rows(with duplicate) but in Query will return only 2 rows.

What can i do to get 3 rows?

THanks

What I have tried:

Right now, I'm getting only 2 rows.


The splitstring function is splitting the strings into 3 rows.
Posted
Updated 27-Jan-17 4:01am
Comments
Naga Sindhura 31-Jan-17 7:13am    
select SKU,PRODUCTNAME,PRODUCTVARIANT from tbl_product where product in(SELECT * FROM STRING_SPLIT('ewew,slk,ewew',','))
STRING_SPLIT only works when you have SQL SERVER 2016

Use a JOIN instead:
SQL
select
    P.SKU,
    P.PRODUCTNAME,
    P.PRODUCTVARIANT 
from 
    tbl_product As P
    INNER JOIN splitstring('ewew,slk,ewew') As S
    ON S.VALUE = P.SKU
;

NB: You'll obviously need to replace S.VALUE with the correct column name returned from your splitstring function.
 
Share this answer
 
Comments
Mr_cool 2-Jun-17 3:25am    
This is the function


Create FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END
Richard Deeming 2-Jun-17 6:51am    
So as I said, replace S.VALUE with S.Name.
Mr_cool 3-Jun-17 8:26am    
Thanks. Worked fine..
The query seem ok.

Can you test the following query?
SQL
select  SKU,PRODUCTNAME,PRODUCTVARIANT from tbl_product where SKU  in ('ewew','slk')

The query should produce three rows. If it still two that mean that could be data issue on the tbl_product table. It could be extra space on the SKU or something.

If the query return 3 rows that could signal that the splitstring function might be an issue.
 
Share this answer
 
Comments
Mr_cool 2-Jun-17 3:25am    
I dont think this will work for me.. Thanks anyways

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900