March 15, 2013 at 5:21 pm
I have a string column very long with multiple underscores in between. My requirement is to only select those columns where the string between second and third underscore matches a certain criteria.
For instances lets say The Compare Values is "RIC" AND I have a row value DMV1004/343M.TR3432_PLC089_RIC_9843CL
Here i need to check if the string between the 2nd and 3rd underscore is RIC which in this case is true.So i would need to compare and if true insert that value. How would i do that efficiently? thnx.
March 15, 2013 at 5:38 pm
Not saying how efficient it is but this may work:
declare @TestStr varchar(128) = 'DMV1004/343M.TR3432_PLC089_RIC_9843CL';
select @TestStr where @TestStr like '%[_]%[_]RIC[_]%';
set @TestStr = 'DMV1004/343M.TR3432_PLC089_RIC1_9843CL';
select @TestStr where @TestStr like '%[_]%[_]RIC[_]%';
March 15, 2013 at 5:44 pm
Lynn Pettis (3/15/2013)
Not saying how efficient it is but this may work:
declare @TestStr varchar(128) = 'DMV1004/343M.TR3432_PLC089_RIC_9843CL';
select @TestStr where @TestStr like '%[_]%[_]RIC[_]%';
set @TestStr = 'DMV1004/343M.TR3432_PLC089_RIC1_9843CL';
select @TestStr where @TestStr like '%[_]%[_]RIC[_]%';
Thanks Lynn, How would i make this dynamic...lets say if the search criteria for the string was between 3 and 4 instead of 2 and 3 based on another parameter. how would this work..i may have to go that route ...cause based on a client the search criteria varies and there are mulitple values to loook for ..like RIC,FED,SRP...u get the idea ...thanks again.
March 15, 2013 at 6:50 pm
declare @TestStr varchar(128) = 'DMV1004/343M.TR3432_PLC089_RIC_9843CL';
select @TestStr
from dbo.DelimitedSplit8K(@TestStr,'_')
where ItemNumber = 3
and Item = 'RIC'
You can get DelimitedSplit8K from here : http://www.sqlservercentral.com/articles/72993/
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy