SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 find string in string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nord
Posting Yak Master

126 Posts

Posted - 11/15/2013 :  09:15:29  Show Profile  Reply with Quote
Hi,
I have problem with find string in string,for example:
string :
'3,4,5,6,7'
in string
'2,2.5,3,4,5,6,7,7.5,8'
I wrote like that ,but its doesnt work:
CHARINDEX (dbo.Pivot_UnPivot.all_size_PU ,(','+ #tmp_table1.aa + ',') ,1)>0
Thanks

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 11/15/2013 :  09:57:41  Show Profile  Reply with Quote
quote:
Originally posted by nord

Hi,
I have problem with find string in string,for example:
string :
'3,4,5,6,7'
in string
'2,2.5,3,4,5,6,7,7.5,8'
I wrote like that ,but its doesnt work:
CHARINDEX (dbo.Pivot_UnPivot.all_size_PU ,(','+ #tmp_table1.aa + ',') ,1)>0
Thanks


What is the output you are expecting to get? If you just want to test for the existence, you can do it like this:
WHERE
   ','+@SourceString+',' LIKE '%,'+@YourSearchTerm+',%'
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 11/15/2013 :  10:22:04  Show Profile  Reply with Quote
actually,not,beacose if is exist this querry select another column and I see a lot of nulls in the columns

IF (SELECT object_id('TempDB..#tmp_table0')) IS NOT NULL
DROP TABLE #tmp_table0

IF (SELECT object_id('TempDB..#tmp_table')) IS NOT NULL
DROP TABLE #tmp_table

IF (SELECT object_id('TempDB..#tmp_table1')) IS NOT NULL
DROP TABLE #tmp_table1

IF (SELECT object_id('TempDB..#tmp_tablesizecd')) IS NOT NULL
DROP TABLE #tmp_tablesizecd

IF (SELECT object_id('TempDB..#tmp_tableresult')) IS NOT NULL
DROP TABLE #tmp_tableresult

-----find all style and min color code for unique size
select IpStyleId
,MIN(colorcode) as minColorCode
into #tmp_table0
from [ITR-SQL].[Yellow].[dbo].ItemMaster
where CustSize<>'998' AND ISNUMERIC(CustSize)=1 -- and patindex('%[0-9]%',CustSize)>0
group by IpStyleId


select *
into #tmp_table
from(
select im.IpStyleId
,CustSize
,ROW_NUMBER() OVER(PARTITION BY im.IpStyleId order by CAST(CustSize AS FLOAT) ) AS row_no

from [ITR-SQL].[Yellow].[dbo].ItemMaster IM
inner join #tmp_table0 TT on IM.IpStyleId=tt.IpStyleId and IM.ColorCode=tt.minColorCode
where CustSize not in ('998') AND ISNUMERIC(CustSize)=1 --and patindex('%[0-9]%',CustSize)>0
)main
pivot
(max(custsize) for row_no in ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14],[15]))pq

select IpStyleId
, case when [2] IS null then [1]
when [3] IS null then [1] + ',' + [2]
when [4] IS null then [1] + ',' + [2]+ ',' + [3]
when [5] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]
when [6] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]
when [7] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]
when IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]
when [9] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' +
when [10] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]
when [11] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]+ ',' + [10]
when [12] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]+ ',' + [10]+ ',' + [11]
when [13] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]+ ',' + [10]+ ',' + [11]+ ',' + [12]
when [14] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]+ ',' + [10]+ ',' + [11]+ ',' + [12]+ ',' + [13]
when [15] IS null then [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]+ ',' + [10]+ ',' + [11]+ ',' + [12]+ ',' + [13]+ ',' + [14]
else [1] + ',' + [2]+ ',' + [3]+ ',' + [4]+ ',' + [5]+ ',' + [6]+ ',' + [7]+ ',' + + ',' + [9]+ ',' + [10]+ ',' + [11]+ ',' + [12]+ ',' + [13]+ ',' + [14] + ',' + [15] end as aa
into #tmp_table1
from #tmp_table


select Size_Cd
,case when Size_Desc02='' then Size_Desc01
when Size_Desc03='' then Size_Desc01 + ',' + Size_Desc02
when Size_Desc04='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03
when Size_Desc05='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04
when Size_Desc06='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05
when Size_Desc07='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06
when Size_Desc08='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07
when Size_Desc09='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08
when Size_Desc10='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09
when Size_Desc11='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09+ ',' + Size_Desc10
when Size_Desc12='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09+ ',' + Size_Desc10+ ',' + Size_Desc11
when Size_Desc13='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09+ ',' + Size_Desc10+ ',' + Size_Desc11+ ',' + Size_Desc12
when Size_Desc14='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09+ ',' + Size_Desc10+ ',' + Size_Desc11+ ',' + Size_Desc12+ ',' + Size_Desc13
when Size_Desc15='' then Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09+ ',' + Size_Desc10+ ',' + Size_Desc11+ ',' + Size_Desc12+ ',' + Size_Desc13+ ',' + Size_Desc14
else Size_Desc01 + ',' + Size_Desc02+ ',' + Size_Desc03+ ',' + Size_Desc04+ ',' + Size_Desc05+ ',' + Size_Desc06+ ',' + Size_Desc07+ ',' + Size_Desc08+ ',' + Size_Desc09+ ',' + Size_Desc10+ ',' + Size_Desc11+ ',' + Size_Desc12+ ',' + Size_Desc13+ ',' + Size_Desc14+ ',' + Size_Desc15 end as aa
into #tmp_tablesizecd
from Yellow_Epicor.dbo.size_ms_sql_01


--select * from [ITR-SQL].[Yellow].[dbo].ItemMaster
--where IpStyleId like '%33800901%'
--ORDER BY sk_ItemId

--SELECT * FROM #tmp_table1
select IpStyleId, Size
into #tmp_tableresult
from #tmp_table1, dbo.Pivot_UnPivot
where --patindex((','+ #tmp_table1.aa + ',') ,dbo.Pivot_UnPivot.all_size_PU )>0
--CHARINDEX (dbo.Pivot_UnPivot.all_size_PU ,(','+ #tmp_table1.aa + ',') ,1)>0
CHARINDEX ((','+ #tmp_table1.aa + ',') ,dbo.Pivot_UnPivot.all_size_PU,1)>0
-- Create temp table to link season to style
SELECT DISTINCT
'UP' --01 Record Type ('UP')
,'A' --02 Action Type ('A')
,vm.sk_Vendor --03 Vendor Code
,sm.VendorStyleCode --04 Vendor Style
,im.ColorCode --05 Color Code
,'' --06
,'' --07
,'' --08
,'' --09
,'' --10
--,im.CustSize --11 Size Category Code
--,case
----when cast(im.CustSize AS float)>15 then '0000'
-- when PIVOT_RES.Style IS not null then PIVOT_RES.size
-- else 'OS' end as size_grid --12 Style Size Code
,'' --13
,'' --14
,LEFT(im.ItemId,LEN(im.ItemId)-1) --15 UPC #
,'I' --16 UPC Type ('I' – In-house UPC)
,'' --17
,'' --18
,'' --19
,rs.Rejected_Style --Style Code
,CASE im.CustSize
WHEN '999' THEN 'OS'
ELSE im.CustSize
END as CustSize
,#tmp_tableresult.size
FROM [ITR-SQL].[Yellow].[dbo].StyleMaster sm
INNER JOIN Yellow_Epicor.dbo.Rejected_Style rs on substring(sm.IpStyleCode,1,8)=rs.Rejected_Style
--INNER JOIN GY_StylesEpicorSizeCodes sc ON LEFT(sm.IpStyleCode,8) = sc.Style --table faite avec les Size Scales de Yelibeth
INNER JOIN [ITR-SQL].[Yellow].[dbo].VendorMaster vm ON sm.Vendor = vm.Vendor
INNER JOIN [ITR-SQL].[Yellow].[dbo].ItemMaster im ON im.IpStyleId = sm.IpStyleId
left JOIN #tmp_tableresult ON im.IpStyleId = #tmp_tableresult.IpStyleId
--on rs.Rejected_Style=PIVOT_RES.Style



WHERE

RIGHT(im.ItemId,1) IN ('F', 'Y')
AND im.CustSize <> '998'
--AND LEFT(sm.SubClass,2) <> '99'

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/15/2013 :  11:48:22  Show Profile  Reply with Quote
Your latest query doesnt have much relation to the first posted query.
Please tell us your actual requirement in below format with sample data and required output
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000