Select query getting issue in SQL Server 2008 R2 - Stack Overflow

admin管理员组

文章数量:1122832

Issue was

String or binary data would be truncated.

This is my query:

select isnull(sum(BVAMT),0) BV 
from TBLMBUSINESS b 
where [STATUS] in('01') and b.MEMBERID in(
    select [MEMBERID] 
    from dbo.[TBLMEMBER] m 
    where m.[PATH] like '%ALC14119/ALC14118/ALC14117/ALC14116/ALC14115/ALC14114/ALC14113/ALC14112/ALC14111/ALC14110/ALC14064/ALC14063/ALC14062/ALC14061/ALC14060/ALC14059/ALC14058/ALC14057/ALC14056/ALC14055/ALC14054/ALC14053/ALC14052/ALC14051/ALC14050/ALC14049/ALC14009/ALC14008/ALC14007/ALC14006/ALC14005/ALC14004/ALC14003/ALC14002/ALC14001/ALC14000/ALC13999/ALC13998/ALC13997/ALC13996/ALC13995/ALC13994/ALC13993/ALC13992/ALC13991/ALC13990/ALC13989/ALC13988/ALC13987/ALC13986/ALC13985/ALC13984/ALC13983/ALC13982/ALC13981/ALC13980/ALC13979/ALC13978/ALC13977/ALC13976/ALC13975/ALC13974/ALC13973/ALC13972/ALC13971/ALC13970/ALC13969/ALC13968/ALC13967/ALC13966/ALC13965/ALC13964/ALC13963/ALC13962/ALC13961/ALC13960/ALC13959/ALC13958/ALC13957/ALC13956/ALC13955/ALC13954/ALC13953/ALC13952/ALC13951/ALC13950/ALC13949/ALC13948/ALC13947/ALC13920/ALC13903/ALC13902/ALC13901/ALC13900/ALC13899/ALC13897/ALC13895/ALC13894/ALC13893/ALC13892/ALC13891/ALC13890/ALC13889/ALC13888/ALC13887/ALC13886/ALC13874/ALC13873/ALC13872/ALC13871/ALC13870/ALC13869/ALC13868/ALC13867/ALC13866/ALC13865/ALC13864/ALC13863/ALC13862/ALC13861/ALC13860/ALC13859/ALC13858/ALC13807/ALC13806/ALC13805/ALC13804/ALC13803/ALC13802/ALC13801/ALC13800/ALC13799/ALC13798/ALC13797/ALC13796/ALC13795/ALC13789/ALC13788/ALC13787/ALC13786/ALC13785/ALC13784/ALC13783/ALC13782/ALC13781/ALC13757/ALC13756/ALC13755/ALC13754/ALC13753/ALC13752/ALC13751/ALC13750/ALC13749/ALC13746/ALC13745/ALC13744/ALC13743/ALC13742/ALC13741/ALC13740/ALC13739/ALC13738/ALC13737/ALC13736/ALC13735/ALC13734/ALC13733/ALC13732/ALC13731/ALC13730/ALC13729/ALC13728/ALC13727/ALC13726/ALC13725/ALC13724/ALC13722/ALC13721/ALC13714/ALC13713/ALC13712/ALC13711/ALC13710/ALC13709/ALC13708/ALC13707/ALC13706/ALC13705/ALC13704/ALC13703/ALC13702/ALC13701/ALC13700/ALC13699/ALC13698/ALC13697/ALC13696/ALC13695/ALC13694/ALC13553/ALC13552/ALC13512/ALC13511/ALC13509/ALC13508/ALC13507/ALC13506/ALC13505/ALC13504/ALC13503/ALC13502/ALC13498/ALC13497/ALC13496/ALC13495/ALC13494/ALC13493/ALC13492/ALC13491/ALC13490/ALC13489/ALC13488/ALC13487/ALC13486/ALC13485/ALC13483/ALC13482/ALC13480/ALC13479/ALC13478/ALC13477/ALC13476/ALC13475/ALC13474/ALC13473/ALC13472/ALC13471/ALC13469/ALC13468/ALC13250/ALC13249/ALC13248/ALC13247/ALC13246/ALC13245/ALC13244/ALC13243/ALC13242/ALC13241/ALC13240/ALC13239/ALC13238/ALC13237/ALC13236/ALC13235/ALC13234/ALC13233/ALC13232/ALC13231/ALC13230/ALC13229/ALC13228/ALC13227/ALC13226/ALC13225/ALC13224/ALC13223/ALC13222/ALC13221/ALC13220/ALC13219/ALC13218/ALC13217/ALC13216/ALC13215/ALC13214/ALC13213/ALC13212/ALC13211/ALC13210/ALC13209/ALC13208/ALC13207/ALC13206/ALC13205/ALC13204/ALC13203/ALC13202/ALC13201/ALC13200/ALC13199/ALC13198/ALC13197/ALC13196/ALC13195/ALC13194/ALC13193/ALC13192/ALC13191/ALC13190/ALC13189/ALC13188/ALC13187/ALC13186/ALC13185/ALC13184/ALC13183/ALC13182/ALC13181/ALC13180/ALC13179/ALC13178/ALC13177/ALC13176/ALC13175/ALC13174/ALC13173/ALC13172/ALC13171/ALC13170/ALC13169/ALC13168/ALC13167/ALC13166/ALC13165/ALC13164/ALC13163/ALC13162/ALC13161/ALC13160/ALC13159/ALC13157/ALC13158/ALC13156/ALC13155/ALC13154/ALC13153/ALC13152/ALC13151/ALC13150/ALC13149/ALC13148/ALC13147/ALC13146/ALC13145/ALC13144/ALC13143/ALC13142/ALC13141/ALC13140/ALC13139/ALC13138/ALC13137/ALC13136/ALC13135/ALC13134/ALC13133/ALC13132/ALC13131/ALC13130/ALC13129/ALC13128/ALC13127/ALC13126/ALC13125/ALC13124/ALC13123/ALC13122/ALC13121/ALC13120/ALC13119/ALC13118/ALC13117/ALC13116/ALC13115/ALC13114/ALC13113/ALC13112/ALC13111/ALC13110/ALC13109/ALC13108/ALC13107/ALC13106/ALC13105/ALC13104/ALC13103/ALC13102/ALC13101/ALC13100/ALC13099/ALC13098/ALC13097/ALC13096/ALC13095/ALC13094/ALC13093/ALC13092/ALC13091/ALC13090/ALC13035/ALC13034/ALC13033/ALC13032/ALC13031/ALC13030/ALC13029/ALC13028/ALC13020/ALC13019/ALC13018/ALC13017/ALC13016/ALC13015/ALC13014/ALC13013/ALC13012/ALC13011/ALC13010/ALC13009/ALC13008/ALC13007/ALC13006/ALC13005/ALC13004/ALC13003/ALC13002/ALC13001/ALC13000/ALC12999/ALC12998/ALC12997/ALC12996/ALC12995/ALC12994/ALC12993/ALC12992/ALC12991/ALC12990/ALC12989/ALC12988/ALC12987/ALC12986/ALC12985/ALC12984/ALC12983/ALC12982/ALC12981/ALC12980/ALC12979/ALC12978/ALC12977/ALC12976/ALC12975/ALC12974/ALC12973/ALC12972/ALC12971/ALC12970/ALC12969/ALC12968/ALC12967/ALC12966/ALC12965/ALC12964/ALC12963/ALC12962/ALC12961/ALC12960/ALC12959/ALC12958/ALC12957/ALC12956/ALC12955/ALC12954/ALC12953/ALC12952/ALC12951/ALC12950/ALC12949/ALC12948/ALC12947/ALC12946/ALC12945/ALC12944/ALC12943/ALC12942/ALC12941/ALC12939/ALC12938/ALC12937/ALC12936/ALC12935/ALC12934/ALC12933/ALC12932/ALC12931/ALC12930/ALC12929/ALC12928/ALC12927/ALC12926/ALC12925/ALC12924/ALC12923/ALC12922/ALC12921/ALC12920/ALC12919/ALC12918/ALC12917/ALC12916/ALC12915/ALC12914/ALC12913/ALC12912/ALC12911/ALC12910/ALC12909/ALC12908/ALC12907/ALC12906/ALC12905/ALC12904/ALC12901/ALC12900/ALC12899/ALC12898/ALC12897/ALC12896/ALC12895/ALC12894/ALC12893/ALC12794/ALC12793/ALC12792/ALC12791/ALC12790/ALC12789/ALC12788/ALC12707/ALC12706/ALC12699/ALC12698/ALC12697/ALC12696/ALC12695/ALC12694/ALC12684/ALC12683/ALC12682/ALC12681/ALC12680/ALC12679/ALC12675/ALC12674/ALC12673/ALC12672/ALC12671/ALC12670/ALC12669/ALC12668/ALC12667/ALC12666/ALC12665/ALC12580/ALC12579/ALC12578/ALC12577/ALC12576/ALC12575/ALC12574/ALC12573/ALC12572/ALC12571/ALC12570/ALC12569/ALC12568/ALC12567/ALC12566/ALC12565/ALC12564/ALC12542/ALC12541/ALC12540/ALC12539/ALC12538/ALC12537/ALC12536/ALC12535/ALC12534/ALC12533/ALC12532/ALC12531/ALC12530/ALC12264/ALC12263/ALC12262/ALC12261/ALC12260/ALC12259/ALC12258/ALC12257/ALC12256/ALC12254/ALC11946/ALC11945/ALC11944/ALC11943/ALC11942/ALC11941/ALC11940/ALC11939/ALC11938/ALC11937/ALC11936/ALC11935/ALC11934/ALC11933/ALC11932/ALC11931/ALC11930/ALC11929/ALC11928/ALC11927/ALC11926/ALC11925/ALC11924/ALC11923/ALC11922/ALC11921/ALC11920/ALC11919/ALC11918/ALC11917/ALC11916/ALC11886/ALC11885/ALC11884/ALC11883/ALC11882/ALC11881/ALC11880/ALC11879/ALC11878/ALC11877/ALC11876/ALC11875/ALC11874/ALC11873/ALC11872/ALC11871/ALC11870/ALC11869/ALC11868/ALC11867/ALC11866/ALC11865/ALC11864/ALC11863/ALC11862/ALC11861/ALC11860/ALC11859/ALC11858/ALC11857/ALC11856/ALC11855/ALC11854/ALC11853/ALC11852/ALC11851/ALC11850/ALC11849/ALC11848/ALC11847/ALC11846/ALC11845/ALC11844/ALC11843/ALC11842/ALC11841/ALC11840/ALC11839/ALC11838/ALC11837/ALC11836/ALC11835/ALC11834/ALC11833/ALC11832/ALC11831/ALC11830/ALC11829/ALC11828/ALC11827/ALC11826/ALC11825/ALC11824/ALC11823/ALC11822/ALC11821/ALC11820/ALC11819/ALC11818/ALC11817/ALC11816/ALC11815/ALC11814/ALC11813/ALC11812/ALC11810/ALC11809/ALC11808/ALC11807/ALC11806/ALC11805/ALC11804/ALC11803/ALC11802/ALC11801/ALC11800/ALC11799/ALC11798/ALC11797/ALC11796/ALC11795/ALC11794/ALC11793/ALC11792/ALC11791/ALC11790/ALC11789/ALC11788/ALC11787/ALC11786/ALC11785/ALC11784/ALC11783/ALC11782/ALC11781/ALC11780/ALC11779/ALC11778/ALC11777/ALC11776/ALC11775/ALC11774/ALC11773/ALC11748/ALC11747/ALC11746/ALC11745/ALC11744/ALC11743/ALC11742/ALC11741/ALC11740/ALC11739/ALC11738/ALC11737/ALC11736/ALC11735/ALC11734/ALC11733/ALC11732/ALC11731/ALC11730/ALC11726/ALC11725/ALC11724/ALC11723/ALC11722/ALC11721/ALC11720/ALC11719/ALC11718/ALC11717/ALC11716/ALC11715/ALC11714/ALC11713/ALC11712/ALC11711/ALC11710/ALC11709/ALC11708/ALC11707/ALC11706/ALC11705/ALC11704/ALC11703/ALC11702/ALC11701/ALC11700/ALC11699/ALC11698/ALC11697/ALC11696/ALC11680/ALC11679/ALC11678/ALC11677/ALC11676/ALC11675/ALC11674/ALC11673/ALC11672/ALC11671/ALC11670/ALC11669/ALC11668/ALC11667/ALC11666/ALC11665/ALC11664/ALC11663/ALC11662/ALC11661/ALC11660/ALC11659/ALC11658/ALC11657/ALC11656/ALC11655/ALC11654/ALC11653/ALC11652/ALC11651/ALC11650/ALC11649/ALC11648/ALC11647/ALC11646/ALC11645/ALC11644/ALC11643/ALC11642/ALC11640/ALC11639/ALC11638/ALC11637/ALC11636/ALC11635/ALC11634/ALC11633/ALC11632/ALC11631/ALC11630/ALC11629/ALC11628/ALC11627/ALC11626/ALC11625/ALC11624/ALC11623/ALC11622/ALC11621/ALC11620/ALC11619/ALC11618/ALC11617/ALC11616/ALC11615/ALC11614/ALC11613/ALC11612/ALC11610/ALC11609/ALC11608/ALC11607/ALC11605/ALC11604/ALC11603/ALC11602/ALC11601/ALC11600/ALC11599/ALC11598/ALC11597/ALC11596/ALC11595/ALC11594/ALC11593/ALC11592/ALC11591/ALC11590/ALC11589/ALC11588/ALC11587/ALC11586/ALC11137/ALC10841/ALC9693/ALC9544/ALC9495/ALC9452/ALC9054/ALC9052/ALC9048/ALC8898/ALC8887/ALC8271/ALC8216/ALC5282/ALC5067/ALC4960/ALC4958/ALC4956/ALC4880/ALC4333/ALC3975/ALC3939/ALC3598/ALC3243/ALC2314/ALC1881/ALC1835/ALC1764/ALC1676/ALC1644/ALC1643/ALC1642/ALC1641/ALC1622/ALC1621/ALC1620/ALC1521/ALC1520/ALC1473/ALC1386/ALC1367/ALC1365/ALC1363/ALC1362/ALC1361/ALC1359/ALC1358/ALC1357/ALC1355/ALC1354/ALC1353/ALC1351/ALC1350/ALC1348/ALC1347/ALC1346/ALC1345/ALC1344/ALC1343/ALC1342/ALC1341/ALC1340/ALC1337/ALC1335/ALC1333/ALC1327/ALC1325/ALC1324/ALC1322/ALC1320/ALC1317/ALC1314/ALC1312/ALC1310/ALC1307/ALC1305/ALC1304/ALC1296/ALC1293/ALC1291/ALC1290/ALC1289/ALC1287/ALC1286/ALC1285/ALC1284/ALC1283/ALC1246/ALC1238/ALC1237/ALC1228/ALC1224/ALC1206/ALC1205/ALC1203/ALC1201/ALC1178/ALC1177/ALC1175/ALC1174/ALC1122/ALC1089/ALC1088/ALC1055/ALC1049/ALC1040/ALC1039/ALC1029/ALC1028/ALC1026/ALC1025/ALC1024/ALC1022/ALC1021/ALC1020/ALC1019/ALC1018/ALC1017/ALC1016/ALC1015/ALC1014/ALC1013/ALC1012/ALC1008/ALC1007/ALC1006/ALC1004/ALC1003/ALC1002/ALC1000/01'
)

Structure of table tblmember:

CREATE TABLE [dbo].[TBLMEMBER]
(
    [TRANNO] [int] IDENTITY(1,1) NOT NULL,
    [PARENTID] [nvarchar](10) NULL,
    [ACID] [nvarchar](10) NULL,
    [MEMBERID] [nvarchar](10) NOT NULL,
    [MEMNAME] [nvarchar](70) NULL,
    [PRINTNAME] [nvarchar](50) NULL,
    [GENDER] [nchar](10) NULL,
    [GROUP] [nchar](2) NULL,
    [MOBILE] [nchar](10) NULL,
    [PANNO] [varchar](20) NULL,
    [PATH] [nvarchar](max) NULL,
    [REFERRBY] [nchar](50) NULL,
    [REFERBY] [varchar](10) NULL,
    [DATEJOIN] [datetime] NULL,
    [DIPO] [varchar](5) NULL,

Table structure of mbusiness

CREATE TABLE [dbo].[TBLMBUSINESS]
(
    [TRNDATENP] [nvarchar](20) NULL,
    [TRNDATE] [date] NOT NULL,
    [DATETIME] [datetime] NOT NULL,
    [VCHRNO] [nvarchar](10) NOT NULL,
    [BILAMT] [money] NOT NULL,
    [BVAMT] [money] NOT NULL,
    [DISCOUNT] [money] NULL,
    [AMOUNT] [money] NULL,
    [BILLCAT] [money] NULL,
    [MEMBERID] [nvarchar](15) NOT NULL,
    [STATUS] [char](2) NULL,
    [ASTS] [char](2) NULL,
    [DIPO] [varchar](3) NULL,
    [VOID] [tinyint] NULL,
    [LNEXT] [money] NULL,
    [RNEXT] [money] NULL,
    [DS] [char](3) NULL,
    [CFID] [varchar](4) NULL
) ON [PRIMARY]

This is a downline group values to calculate all total of downline. there would be more peoples unlimited.

1. There are so many peoples in downline
2. Business volume of downline needs to be added in total of downline

Issue was

String or binary data would be truncated.

This is my query:

select isnull(sum(BVAMT),0) BV 
from TBLMBUSINESS b 
where [STATUS] in('01') and b.MEMBERID in(
    select [MEMBERID] 
    from dbo.[TBLMEMBER] m 
    where m.[PATH] like '%ALC14119/ALC14118/ALC14117/ALC14116/ALC14115/ALC14114/ALC14113/ALC14112/ALC14111/ALC14110/ALC14064/ALC14063/ALC14062/ALC14061/ALC14060/ALC14059/ALC14058/ALC14057/ALC14056/ALC14055/ALC14054/ALC14053/ALC14052/ALC14051/ALC14050/ALC14049/ALC14009/ALC14008/ALC14007/ALC14006/ALC14005/ALC14004/ALC14003/ALC14002/ALC14001/ALC14000/ALC13999/ALC13998/ALC13997/ALC13996/ALC13995/ALC13994/ALC13993/ALC13992/ALC13991/ALC13990/ALC13989/ALC13988/ALC13987/ALC13986/ALC13985/ALC13984/ALC13983/ALC13982/ALC13981/ALC13980/ALC13979/ALC13978/ALC13977/ALC13976/ALC13975/ALC13974/ALC13973/ALC13972/ALC13971/ALC13970/ALC13969/ALC13968/ALC13967/ALC13966/ALC13965/ALC13964/ALC13963/ALC13962/ALC13961/ALC13960/ALC13959/ALC13958/ALC13957/ALC13956/ALC13955/ALC13954/ALC13953/ALC13952/ALC13951/ALC13950/ALC13949/ALC13948/ALC13947/ALC13920/ALC13903/ALC13902/ALC13901/ALC13900/ALC13899/ALC13897/ALC13895/ALC13894/ALC13893/ALC13892/ALC13891/ALC13890/ALC13889/ALC13888/ALC13887/ALC13886/ALC13874/ALC13873/ALC13872/ALC13871/ALC13870/ALC13869/ALC13868/ALC13867/ALC13866/ALC13865/ALC13864/ALC13863/ALC13862/ALC13861/ALC13860/ALC13859/ALC13858/ALC13807/ALC13806/ALC13805/ALC13804/ALC13803/ALC13802/ALC13801/ALC13800/ALC13799/ALC13798/ALC13797/ALC13796/ALC13795/ALC13789/ALC13788/ALC13787/ALC13786/ALC13785/ALC13784/ALC13783/ALC13782/ALC13781/ALC13757/ALC13756/ALC13755/ALC13754/ALC13753/ALC13752/ALC13751/ALC13750/ALC13749/ALC13746/ALC13745/ALC13744/ALC13743/ALC13742/ALC13741/ALC13740/ALC13739/ALC13738/ALC13737/ALC13736/ALC13735/ALC13734/ALC13733/ALC13732/ALC13731/ALC13730/ALC13729/ALC13728/ALC13727/ALC13726/ALC13725/ALC13724/ALC13722/ALC13721/ALC13714/ALC13713/ALC13712/ALC13711/ALC13710/ALC13709/ALC13708/ALC13707/ALC13706/ALC13705/ALC13704/ALC13703/ALC13702/ALC13701/ALC13700/ALC13699/ALC13698/ALC13697/ALC13696/ALC13695/ALC13694/ALC13553/ALC13552/ALC13512/ALC13511/ALC13509/ALC13508/ALC13507/ALC13506/ALC13505/ALC13504/ALC13503/ALC13502/ALC13498/ALC13497/ALC13496/ALC13495/ALC13494/ALC13493/ALC13492/ALC13491/ALC13490/ALC13489/ALC13488/ALC13487/ALC13486/ALC13485/ALC13483/ALC13482/ALC13480/ALC13479/ALC13478/ALC13477/ALC13476/ALC13475/ALC13474/ALC13473/ALC13472/ALC13471/ALC13469/ALC13468/ALC13250/ALC13249/ALC13248/ALC13247/ALC13246/ALC13245/ALC13244/ALC13243/ALC13242/ALC13241/ALC13240/ALC13239/ALC13238/ALC13237/ALC13236/ALC13235/ALC13234/ALC13233/ALC13232/ALC13231/ALC13230/ALC13229/ALC13228/ALC13227/ALC13226/ALC13225/ALC13224/ALC13223/ALC13222/ALC13221/ALC13220/ALC13219/ALC13218/ALC13217/ALC13216/ALC13215/ALC13214/ALC13213/ALC13212/ALC13211/ALC13210/ALC13209/ALC13208/ALC13207/ALC13206/ALC13205/ALC13204/ALC13203/ALC13202/ALC13201/ALC13200/ALC13199/ALC13198/ALC13197/ALC13196/ALC13195/ALC13194/ALC13193/ALC13192/ALC13191/ALC13190/ALC13189/ALC13188/ALC13187/ALC13186/ALC13185/ALC13184/ALC13183/ALC13182/ALC13181/ALC13180/ALC13179/ALC13178/ALC13177/ALC13176/ALC13175/ALC13174/ALC13173/ALC13172/ALC13171/ALC13170/ALC13169/ALC13168/ALC13167/ALC13166/ALC13165/ALC13164/ALC13163/ALC13162/ALC13161/ALC13160/ALC13159/ALC13157/ALC13158/ALC13156/ALC13155/ALC13154/ALC13153/ALC13152/ALC13151/ALC13150/ALC13149/ALC13148/ALC13147/ALC13146/ALC13145/ALC13144/ALC13143/ALC13142/ALC13141/ALC13140/ALC13139/ALC13138/ALC13137/ALC13136/ALC13135/ALC13134/ALC13133/ALC13132/ALC13131/ALC13130/ALC13129/ALC13128/ALC13127/ALC13126/ALC13125/ALC13124/ALC13123/ALC13122/ALC13121/ALC13120/ALC13119/ALC13118/ALC13117/ALC13116/ALC13115/ALC13114/ALC13113/ALC13112/ALC13111/ALC13110/ALC13109/ALC13108/ALC13107/ALC13106/ALC13105/ALC13104/ALC13103/ALC13102/ALC13101/ALC13100/ALC13099/ALC13098/ALC13097/ALC13096/ALC13095/ALC13094/ALC13093/ALC13092/ALC13091/ALC13090/ALC13035/ALC13034/ALC13033/ALC13032/ALC13031/ALC13030/ALC13029/ALC13028/ALC13020/ALC13019/ALC13018/ALC13017/ALC13016/ALC13015/ALC13014/ALC13013/ALC13012/ALC13011/ALC13010/ALC13009/ALC13008/ALC13007/ALC13006/ALC13005/ALC13004/ALC13003/ALC13002/ALC13001/ALC13000/ALC12999/ALC12998/ALC12997/ALC12996/ALC12995/ALC12994/ALC12993/ALC12992/ALC12991/ALC12990/ALC12989/ALC12988/ALC12987/ALC12986/ALC12985/ALC12984/ALC12983/ALC12982/ALC12981/ALC12980/ALC12979/ALC12978/ALC12977/ALC12976/ALC12975/ALC12974/ALC12973/ALC12972/ALC12971/ALC12970/ALC12969/ALC12968/ALC12967/ALC12966/ALC12965/ALC12964/ALC12963/ALC12962/ALC12961/ALC12960/ALC12959/ALC12958/ALC12957/ALC12956/ALC12955/ALC12954/ALC12953/ALC12952/ALC12951/ALC12950/ALC12949/ALC12948/ALC12947/ALC12946/ALC12945/ALC12944/ALC12943/ALC12942/ALC12941/ALC12939/ALC12938/ALC12937/ALC12936/ALC12935/ALC12934/ALC12933/ALC12932/ALC12931/ALC12930/ALC12929/ALC12928/ALC12927/ALC12926/ALC12925/ALC12924/ALC12923/ALC12922/ALC12921/ALC12920/ALC12919/ALC12918/ALC12917/ALC12916/ALC12915/ALC12914/ALC12913/ALC12912/ALC12911/ALC12910/ALC12909/ALC12908/ALC12907/ALC12906/ALC12905/ALC12904/ALC12901/ALC12900/ALC12899/ALC12898/ALC12897/ALC12896/ALC12895/ALC12894/ALC12893/ALC12794/ALC12793/ALC12792/ALC12791/ALC12790/ALC12789/ALC12788/ALC12707/ALC12706/ALC12699/ALC12698/ALC12697/ALC12696/ALC12695/ALC12694/ALC12684/ALC12683/ALC12682/ALC12681/ALC12680/ALC12679/ALC12675/ALC12674/ALC12673/ALC12672/ALC12671/ALC12670/ALC12669/ALC12668/ALC12667/ALC12666/ALC12665/ALC12580/ALC12579/ALC12578/ALC12577/ALC12576/ALC12575/ALC12574/ALC12573/ALC12572/ALC12571/ALC12570/ALC12569/ALC12568/ALC12567/ALC12566/ALC12565/ALC12564/ALC12542/ALC12541/ALC12540/ALC12539/ALC12538/ALC12537/ALC12536/ALC12535/ALC12534/ALC12533/ALC12532/ALC12531/ALC12530/ALC12264/ALC12263/ALC12262/ALC12261/ALC12260/ALC12259/ALC12258/ALC12257/ALC12256/ALC12254/ALC11946/ALC11945/ALC11944/ALC11943/ALC11942/ALC11941/ALC11940/ALC11939/ALC11938/ALC11937/ALC11936/ALC11935/ALC11934/ALC11933/ALC11932/ALC11931/ALC11930/ALC11929/ALC11928/ALC11927/ALC11926/ALC11925/ALC11924/ALC11923/ALC11922/ALC11921/ALC11920/ALC11919/ALC11918/ALC11917/ALC11916/ALC11886/ALC11885/ALC11884/ALC11883/ALC11882/ALC11881/ALC11880/ALC11879/ALC11878/ALC11877/ALC11876/ALC11875/ALC11874/ALC11873/ALC11872/ALC11871/ALC11870/ALC11869/ALC11868/ALC11867/ALC11866/ALC11865/ALC11864/ALC11863/ALC11862/ALC11861/ALC11860/ALC11859/ALC11858/ALC11857/ALC11856/ALC11855/ALC11854/ALC11853/ALC11852/ALC11851/ALC11850/ALC11849/ALC11848/ALC11847/ALC11846/ALC11845/ALC11844/ALC11843/ALC11842/ALC11841/ALC11840/ALC11839/ALC11838/ALC11837/ALC11836/ALC11835/ALC11834/ALC11833/ALC11832/ALC11831/ALC11830/ALC11829/ALC11828/ALC11827/ALC11826/ALC11825/ALC11824/ALC11823/ALC11822/ALC11821/ALC11820/ALC11819/ALC11818/ALC11817/ALC11816/ALC11815/ALC11814/ALC11813/ALC11812/ALC11810/ALC11809/ALC11808/ALC11807/ALC11806/ALC11805/ALC11804/ALC11803/ALC11802/ALC11801/ALC11800/ALC11799/ALC11798/ALC11797/ALC11796/ALC11795/ALC11794/ALC11793/ALC11792/ALC11791/ALC11790/ALC11789/ALC11788/ALC11787/ALC11786/ALC11785/ALC11784/ALC11783/ALC11782/ALC11781/ALC11780/ALC11779/ALC11778/ALC11777/ALC11776/ALC11775/ALC11774/ALC11773/ALC11748/ALC11747/ALC11746/ALC11745/ALC11744/ALC11743/ALC11742/ALC11741/ALC11740/ALC11739/ALC11738/ALC11737/ALC11736/ALC11735/ALC11734/ALC11733/ALC11732/ALC11731/ALC11730/ALC11726/ALC11725/ALC11724/ALC11723/ALC11722/ALC11721/ALC11720/ALC11719/ALC11718/ALC11717/ALC11716/ALC11715/ALC11714/ALC11713/ALC11712/ALC11711/ALC11710/ALC11709/ALC11708/ALC11707/ALC11706/ALC11705/ALC11704/ALC11703/ALC11702/ALC11701/ALC11700/ALC11699/ALC11698/ALC11697/ALC11696/ALC11680/ALC11679/ALC11678/ALC11677/ALC11676/ALC11675/ALC11674/ALC11673/ALC11672/ALC11671/ALC11670/ALC11669/ALC11668/ALC11667/ALC11666/ALC11665/ALC11664/ALC11663/ALC11662/ALC11661/ALC11660/ALC11659/ALC11658/ALC11657/ALC11656/ALC11655/ALC11654/ALC11653/ALC11652/ALC11651/ALC11650/ALC11649/ALC11648/ALC11647/ALC11646/ALC11645/ALC11644/ALC11643/ALC11642/ALC11640/ALC11639/ALC11638/ALC11637/ALC11636/ALC11635/ALC11634/ALC11633/ALC11632/ALC11631/ALC11630/ALC11629/ALC11628/ALC11627/ALC11626/ALC11625/ALC11624/ALC11623/ALC11622/ALC11621/ALC11620/ALC11619/ALC11618/ALC11617/ALC11616/ALC11615/ALC11614/ALC11613/ALC11612/ALC11610/ALC11609/ALC11608/ALC11607/ALC11605/ALC11604/ALC11603/ALC11602/ALC11601/ALC11600/ALC11599/ALC11598/ALC11597/ALC11596/ALC11595/ALC11594/ALC11593/ALC11592/ALC11591/ALC11590/ALC11589/ALC11588/ALC11587/ALC11586/ALC11137/ALC10841/ALC9693/ALC9544/ALC9495/ALC9452/ALC9054/ALC9052/ALC9048/ALC8898/ALC8887/ALC8271/ALC8216/ALC5282/ALC5067/ALC4960/ALC4958/ALC4956/ALC4880/ALC4333/ALC3975/ALC3939/ALC3598/ALC3243/ALC2314/ALC1881/ALC1835/ALC1764/ALC1676/ALC1644/ALC1643/ALC1642/ALC1641/ALC1622/ALC1621/ALC1620/ALC1521/ALC1520/ALC1473/ALC1386/ALC1367/ALC1365/ALC1363/ALC1362/ALC1361/ALC1359/ALC1358/ALC1357/ALC1355/ALC1354/ALC1353/ALC1351/ALC1350/ALC1348/ALC1347/ALC1346/ALC1345/ALC1344/ALC1343/ALC1342/ALC1341/ALC1340/ALC1337/ALC1335/ALC1333/ALC1327/ALC1325/ALC1324/ALC1322/ALC1320/ALC1317/ALC1314/ALC1312/ALC1310/ALC1307/ALC1305/ALC1304/ALC1296/ALC1293/ALC1291/ALC1290/ALC1289/ALC1287/ALC1286/ALC1285/ALC1284/ALC1283/ALC1246/ALC1238/ALC1237/ALC1228/ALC1224/ALC1206/ALC1205/ALC1203/ALC1201/ALC1178/ALC1177/ALC1175/ALC1174/ALC1122/ALC1089/ALC1088/ALC1055/ALC1049/ALC1040/ALC1039/ALC1029/ALC1028/ALC1026/ALC1025/ALC1024/ALC1022/ALC1021/ALC1020/ALC1019/ALC1018/ALC1017/ALC1016/ALC1015/ALC1014/ALC1013/ALC1012/ALC1008/ALC1007/ALC1006/ALC1004/ALC1003/ALC1002/ALC1000/01'
)

Structure of table tblmember:

CREATE TABLE [dbo].[TBLMEMBER]
(
    [TRANNO] [int] IDENTITY(1,1) NOT NULL,
    [PARENTID] [nvarchar](10) NULL,
    [ACID] [nvarchar](10) NULL,
    [MEMBERID] [nvarchar](10) NOT NULL,
    [MEMNAME] [nvarchar](70) NULL,
    [PRINTNAME] [nvarchar](50) NULL,
    [GENDER] [nchar](10) NULL,
    [GROUP] [nchar](2) NULL,
    [MOBILE] [nchar](10) NULL,
    [PANNO] [varchar](20) NULL,
    [PATH] [nvarchar](max) NULL,
    [REFERRBY] [nchar](50) NULL,
    [REFERBY] [varchar](10) NULL,
    [DATEJOIN] [datetime] NULL,
    [DIPO] [varchar](5) NULL,

Table structure of mbusiness

CREATE TABLE [dbo].[TBLMBUSINESS]
(
    [TRNDATENP] [nvarchar](20) NULL,
    [TRNDATE] [date] NOT NULL,
    [DATETIME] [datetime] NOT NULL,
    [VCHRNO] [nvarchar](10) NOT NULL,
    [BILAMT] [money] NOT NULL,
    [BVAMT] [money] NOT NULL,
    [DISCOUNT] [money] NULL,
    [AMOUNT] [money] NULL,
    [BILLCAT] [money] NULL,
    [MEMBERID] [nvarchar](15) NOT NULL,
    [STATUS] [char](2) NULL,
    [ASTS] [char](2) NULL,
    [DIPO] [varchar](3) NULL,
    [VOID] [tinyint] NULL,
    [LNEXT] [money] NULL,
    [RNEXT] [money] NULL,
    [DS] [char](3) NULL,
    [CFID] [varchar](4) NULL
) ON [PRIMARY]

This is a downline group values to calculate all total of downline. there would be more peoples unlimited.

1. There are so many peoples in downline
2. Business volume of downline needs to be added in total of downline

• sql-server

Share Improve this question Follow edited Nov 21, 2024 at 19:05 Thom A 95.3k1111 gold badges5959 silver badges9292 bronze badges asked Nov 21, 2024 at 18:15 Rajendra DhakalRajendra Dhakal 111 silver badge11 bronze badge 4

• 1 Why are do you need to look for a string that's over 9,000 characters long? This sounds like the problem is actually your value/design, not the query. Smells like an XY Problem to me. – Thom A Commented Nov 21, 2024 at 18:19
• 3 Side note, as well: SQL Server 2008 (R2) reached end of life over 5 years ago; you urgently need to upgrade your instance to supported version. – Thom A Commented Nov 21, 2024 at 18:24
• Honestly, looking at the value you are searching for, it feels like you have a significantly denormalised design. – Thom A Commented Nov 21, 2024 at 19:17
• What does downline mean? is the path a list of members? if so you should change your design – Steve Ford Commented Nov 21, 2024 at 22:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

LIKE has a maximum of 8,000 bytes for the Pattern.

Your string is 9,072 characters long.

As you have no trailing wildcard you can do

DECLARE @PathToMatch VARCHAR(Max) = 'ALC14119/ALC14118/ALC14117/ALC14116/ALC14115/ALC14114/ALC14113/ALC14112/ALC14111/ALC14110/ALC14064/ALC14063/ALC14062/ALC14061/ALC14060/ALC14059/ALC14058/ALC14057/ALC14056/ALC14055/ALC14054/ALC14053/ALC14052/ALC14051/ALC14050/ALC14049/ALC14009/ALC14008/ALC14007/ALC14006/ALC14005/ALC14004/ALC14003/ALC14002/ALC14001/ALC14000/ALC13999/ALC13998/ALC13997/ALC13996/ALC13995/ALC13994/ALC13993/ALC13992/ALC13991/ALC13990/ALC13989/ALC13988/ALC13987/ALC13986/ALC13985/ALC13984/ALC13983/ALC13982/ALC13981/ALC13980/ALC13979/ALC13978/ALC13977/ALC13976/ALC13975/ALC13974/ALC13973/ALC13972/ALC13971/ALC13970/ALC13969/ALC13968/ALC13967/ALC13966/ALC13965/ALC13964/ALC13963/ALC13962/ALC13961/ALC13960/ALC13959/ALC13958/ALC13957/ALC13956/ALC13955/ALC13954/ALC13953/ALC13952/ALC13951/ALC13950/ALC13949/ALC13948/ALC13947/ALC13920/ALC13903/ALC13902/ALC13901/ALC13900/ALC13899/ALC13897/ALC13895/ALC13894/ALC13893/ALC13892/ALC13891/ALC13890/ALC13889/ALC13888/ALC13887/ALC13886/ALC13874/ALC13873/ALC13872/ALC13871/ALC13870/ALC13869/ALC13868/ALC13867/ALC13866/ALC13865/ALC13864/ALC13863/ALC13862/ALC13861/ALC13860/ALC13859/ALC13858/ALC13807/ALC13806/ALC13805/ALC13804/ALC13803/ALC13802/ALC13801/ALC13800/ALC13799/ALC13798/ALC13797/ALC13796/ALC13795/ALC13789/ALC13788/ALC13787/ALC13786/ALC13785/ALC13784/ALC13783/ALC13782/ALC13781/ALC13757/ALC13756/ALC13755/ALC13754/ALC13753/ALC13752/ALC13751/ALC13750/ALC13749/ALC13746/ALC13745/ALC13744/ALC13743/ALC13742/ALC13741/ALC13740/ALC13739/ALC13738/ALC13737/ALC13736/ALC13735/ALC13734/ALC13733/ALC13732/ALC13731/ALC13730/ALC13729/ALC13728/ALC13727/ALC13726/ALC13725/ALC13724/ALC13722/ALC13721/ALC13714/ALC13713/ALC13712/ALC13711/ALC13710/ALC13709/ALC13708/ALC13707/ALC13706/ALC13705/ALC13704/ALC13703/ALC13702/ALC13701/ALC13700/ALC13699/ALC13698/ALC13697/ALC13696/ALC13695/ALC13694/ALC13553/ALC13552/ALC13512/ALC13511/ALC13509/ALC13508/ALC13507/ALC13506/ALC13505/ALC13504/ALC13503/ALC13502/ALC13498/ALC13497/ALC13496/ALC13495/ALC13494/ALC13493/ALC13492/ALC13491/ALC13490/ALC13489/ALC13488/ALC13487/ALC13486/ALC13485/ALC13483/ALC13482/ALC13480/ALC13479/ALC13478/ALC13477/ALC13476/ALC13475/ALC13474/ALC13473/ALC13472/ALC13471/ALC13469/ALC13468/ALC13250/ALC13249/ALC13248/ALC13247/ALC13246/ALC13245/ALC13244/ALC13243/ALC13242/ALC13241/ALC13240/ALC13239/ALC13238/ALC13237/ALC13236/ALC13235/ALC13234/ALC13233/ALC13232/ALC13231/ALC13230/ALC13229/ALC13228/ALC13227/ALC13226/ALC13225/ALC13224/ALC13223/ALC13222/ALC13221/ALC13220/ALC13219/ALC13218/ALC13217/ALC13216/ALC13215/ALC13214/ALC13213/ALC13212/ALC13211/ALC13210/ALC13209/ALC13208/ALC13207/ALC13206/ALC13205/ALC13204/ALC13203/ALC13202/ALC13201/ALC13200/ALC13199/ALC13198/ALC13197/ALC13196/ALC13195/ALC13194/ALC13193/ALC13192/ALC13191/ALC13190/ALC13189/ALC13188/ALC13187/ALC13186/ALC13185/ALC13184/ALC13183/ALC13182/ALC13181/ALC13180/ALC13179/ALC13178/ALC13177/ALC13176/ALC13175/ALC13174/ALC13173/ALC13172/ALC13171/ALC13170/ALC13169/ALC13168/ALC13167/ALC13166/ALC13165/ALC13164/ALC13163/ALC13162/ALC13161/ALC13160/ALC13159/ALC13157/ALC13158/ALC13156/ALC13155/ALC13154/ALC13153/ALC13152/ALC13151/ALC13150/ALC13149/ALC13148/ALC13147/ALC13146/ALC13145/ALC13144/ALC13143/ALC13142/ALC13141/ALC13140/ALC13139/ALC13138/ALC13137/ALC13136/ALC13135/ALC13134/ALC13133/ALC13132/ALC13131/ALC13130/ALC13129/ALC13128/ALC13127/ALC13126/ALC13125/ALC13124/ALC13123/ALC13122/ALC13121/ALC13120/ALC13119/ALC13118/ALC13117/ALC13116/ALC13115/ALC13114/ALC13113/ALC13112/ALC13111/ALC13110/ALC13109/ALC13108/ALC13107/ALC13106/ALC13105/ALC13104/ALC13103/ALC13102/ALC13101/ALC13100/ALC13099/ALC13098/ALC13097/ALC13096/ALC13095/ALC13094/ALC13093/ALC13092/ALC13091/ALC13090/ALC13035/ALC13034/ALC13033/ALC13032/ALC13031/ALC13030/ALC13029/ALC13028/ALC13020/ALC13019/ALC13018/ALC13017/ALC13016/ALC13015/ALC13014/ALC13013/ALC13012/ALC13011/ALC13010/ALC13009/ALC13008/ALC13007/ALC13006/ALC13005/ALC13004/ALC13003/ALC13002/ALC13001/ALC13000/ALC12999/ALC12998/ALC12997/ALC12996/ALC12995/ALC12994/ALC12993/ALC12992/ALC12991/ALC12990/ALC12989/ALC12988/ALC12987/ALC12986/ALC12985/ALC12984/ALC12983/ALC12982/ALC12981/ALC12980/ALC12979/ALC12978/ALC12977/ALC12976/ALC12975/ALC12974/ALC12973/ALC12972/ALC12971/ALC12970/ALC12969/ALC12968/ALC12967/ALC12966/ALC12965/ALC12964/ALC12963/ALC12962/ALC12961/ALC12960/ALC12959/ALC12958/ALC12957/ALC12956/ALC12955/ALC12954/ALC12953/ALC12952/ALC12951/ALC12950/ALC12949/ALC12948/ALC12947/ALC12946/ALC12945/ALC12944/ALC12943/ALC12942/ALC12941/ALC12939/ALC12938/ALC12937/ALC12936/ALC12935/ALC12934/ALC12933/ALC12932/ALC12931/ALC12930/ALC12929/ALC12928/ALC12927/ALC12926/ALC12925/ALC12924/ALC12923/ALC12922/ALC12921/ALC12920/ALC12919/ALC12918/ALC12917/ALC12916/ALC12915/ALC12914/ALC12913/ALC12912/ALC12911/ALC12910/ALC12909/ALC12908/ALC12907/ALC12906/ALC12905/ALC12904/ALC12901/ALC12900/ALC12899/ALC12898/ALC12897/ALC12896/ALC12895/ALC12894/ALC12893/ALC12794/ALC12793/ALC12792/ALC12791/ALC12790/ALC12789/ALC12788/ALC12707/ALC12706/ALC12699/ALC12698/ALC12697/ALC12696/ALC12695/ALC12694/ALC12684/ALC12683/ALC12682/ALC12681/ALC12680/ALC12679/ALC12675/ALC12674/ALC12673/ALC12672/ALC12671/ALC12670/ALC12669/ALC12668/ALC12667/ALC12666/ALC12665/ALC12580/ALC12579/ALC12578/ALC12577/ALC12576/ALC12575/ALC12574/ALC12573/ALC12572/ALC12571/ALC12570/ALC12569/ALC12568/ALC12567/ALC12566/ALC12565/ALC12564/ALC12542/ALC12541/ALC12540/ALC12539/ALC12538/ALC12537/ALC12536/ALC12535/ALC12534/ALC12533/ALC12532/ALC12531/ALC12530/ALC12264/ALC12263/ALC12262/ALC12261/ALC12260/ALC12259/ALC12258/ALC12257/ALC12256/ALC12254/ALC11946/ALC11945/ALC11944/ALC11943/ALC11942/ALC11941/ALC11940/ALC11939/ALC11938/ALC11937/ALC11936/ALC11935/ALC11934/ALC11933/ALC11932/ALC11931/ALC11930/ALC11929/ALC11928/ALC11927/ALC11926/ALC11925/ALC11924/ALC11923/ALC11922/ALC11921/ALC11920/ALC11919/ALC11918/ALC11917/ALC11916/ALC11886/ALC11885/ALC11884/ALC11883/ALC11882/ALC11881/ALC11880/ALC11879/ALC11878/ALC11877/ALC11876/ALC11875/ALC11874/ALC11873/ALC11872/ALC11871/ALC11870/ALC11869/ALC11868/ALC11867/ALC11866/ALC11865/ALC11864/ALC11863/ALC11862/ALC11861/ALC11860/ALC11859/ALC11858/ALC11857/ALC11856/ALC11855/ALC11854/ALC11853/ALC11852/ALC11851/ALC11850/ALC11849/ALC11848/ALC11847/ALC11846/ALC11845/ALC11844/ALC11843/ALC11842/ALC11841/ALC11840/ALC11839/ALC11838/ALC11837/ALC11836/ALC11835/ALC11834/ALC11833/ALC11832/ALC11831/ALC11830/ALC11829/ALC11828/ALC11827/ALC11826/ALC11825/ALC11824/ALC11823/ALC11822/ALC11821/ALC11820/ALC11819/ALC11818/ALC11817/ALC11816/ALC11815/ALC11814/ALC11813/ALC11812/ALC11810/ALC11809/ALC11808/ALC11807/ALC11806/ALC11805/ALC11804/ALC11803/ALC11802/ALC11801/ALC11800/ALC11799/ALC11798/ALC11797/ALC11796/ALC11795/ALC11794/ALC11793/ALC11792/ALC11791/ALC11790/ALC11789/ALC11788/ALC11787/ALC11786/ALC11785/ALC11784/ALC11783/ALC11782/ALC11781/ALC11780/ALC11779/ALC11778/ALC11777/ALC11776/ALC11775/ALC11774/ALC11773/ALC11748/ALC11747/ALC11746/ALC11745/ALC11744/ALC11743/ALC11742/ALC11741/ALC11740/ALC11739/ALC11738/ALC11737/ALC11736/ALC11735/ALC11734/ALC11733/ALC11732/ALC11731/ALC11730/ALC11726/ALC11725/ALC11724/ALC11723/ALC11722/ALC11721/ALC11720/ALC11719/ALC11718/ALC11717/ALC11716/ALC11715/ALC11714/ALC11713/ALC11712/ALC11711/ALC11710/ALC11709/ALC11708/ALC11707/ALC11706/ALC11705/ALC11704/ALC11703/ALC11702/ALC11701/ALC11700/ALC11699/ALC11698/ALC11697/ALC11696/ALC11680/ALC11679/ALC11678/ALC11677/ALC11676/ALC11675/ALC11674/ALC11673/ALC11672/ALC11671/ALC11670/ALC11669/ALC11668/ALC11667/ALC11666/ALC11665/ALC11664/ALC11663/ALC11662/ALC11661/ALC11660/ALC11659/ALC11658/ALC11657/ALC11656/ALC11655/ALC11654/ALC11653/ALC11652/ALC11651/ALC11650/ALC11649/ALC11648/ALC11647/ALC11646/ALC11645/ALC11644/ALC11643/ALC11642/ALC11640/ALC11639/ALC11638/ALC11637/ALC11636/ALC11635/ALC11634/ALC11633/ALC11632/ALC11631/ALC11630/ALC11629/ALC11628/ALC11627/ALC11626/ALC11625/ALC11624/ALC11623/ALC11622/ALC11621/ALC11620/ALC11619/ALC11618/ALC11617/ALC11616/ALC11615/ALC11614/ALC11613/ALC11612/ALC11610/ALC11609/ALC11608/ALC11607/ALC11605/ALC11604/ALC11603/ALC11602/ALC11601/ALC11600/ALC11599/ALC11598/ALC11597/ALC11596/ALC11595/ALC11594/ALC11593/ALC11592/ALC11591/ALC11590/ALC11589/ALC11588/ALC11587/ALC11586/ALC11137/ALC10841/ALC9693/ALC9544/ALC9495/ALC9452/ALC9054/ALC9052/ALC9048/ALC8898/ALC8887/ALC8271/ALC8216/ALC5282/ALC5067/ALC4960/ALC4958/ALC4956/ALC4880/ALC4333/ALC3975/ALC3939/ALC3598/ALC3243/ALC2314/ALC1881/ALC1835/ALC1764/ALC1676/ALC1644/ALC1643/ALC1642/ALC1641/ALC1622/ALC1621/ALC1620/ALC1521/ALC1520/ALC1473/ALC1386/ALC1367/ALC1365/ALC1363/ALC1362/ALC1361/ALC1359/ALC1358/ALC1357/ALC1355/ALC1354/ALC1353/ALC1351/ALC1350/ALC1348/ALC1347/ALC1346/ALC1345/ALC1344/ALC1343/ALC1342/ALC1341/ALC1340/ALC1337/ALC1335/ALC1333/ALC1327/ALC1325/ALC1324/ALC1322/ALC1320/ALC1317/ALC1314/ALC1312/ALC1310/ALC1307/ALC1305/ALC1304/ALC1296/ALC1293/ALC1291/ALC1290/ALC1289/ALC1287/ALC1286/ALC1285/ALC1284/ALC1283/ALC1246/ALC1238/ALC1237/ALC1228/ALC1224/ALC1206/ALC1205/ALC1203/ALC1201/ALC1178/ALC1177/ALC1175/ALC1174/ALC1122/ALC1089/ALC1088/ALC1055/ALC1049/ALC1040/ALC1039/ALC1029/ALC1028/ALC1026/ALC1025/ALC1024/ALC1022/ALC1021/ALC1020/ALC1019/ALC1018/ALC1017/ALC1016/ALC1015/ALC1014/ALC1013/ALC1012/ALC1008/ALC1007/ALC1006/ALC1004/ALC1003/ALC1002/ALC1000/01'

And then use

WHERE RIGHT(m.[PATH],LEN(@PathToMatch)) = @PathToMatch

NB CHARINDEX does not help here either as "expressionToFind has an 8000 character limit"

本文标签： Select query getting issue in SQL Server 2008 R2Stack Overflow