Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 find string in string

Author  Topic 

nord
Posting Yak Master

126 Posts
Posted - 2013-11-15 : 09:15:29
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
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-11-15 : 09:57:41
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 - 2013-11-15 : 10:22:04
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

52326 Posts
Posted - 2013-11-15 : 11:48:22
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site