November 17, 2015 at 4:08 am
hello all.
I have a Column called HID with varchar type.for example:'12,3,16'
'16,10,256'
'2,150,110,200'
'5,70,4'
I need to have this result:3 with int type
10 with int type
110 with int type
70 with int type
How can I have this with TSql?
November 17, 2015 at 4:14 am
Just in relation to this value
'2,150,110,200'
Why 110 and not 150,110?
If you had '2,100,110,150,200,123,159602,1568'
What would the correct answer be?
November 17, 2015 at 4:30 am
hello anthony.green.
In your example,I need this result:159602
in fact I always need second part from last:If I have this:14,18,110,120,300 I need this:120
November 17, 2015 at 4:40 am
Number of ways to do it
create table #test(HID varchar (50))
insert into #test values
('12,3,16'),
('16,10,256'),
('2,150,110,200'),
('5,70,4'),
('2,100,110,150,200,123,159602,1568')
select
hid,
charindex(',',reverse(hid)) AS RightMostCommaPosition,
left(hid,len(hid)-charindex(',',reverse(hid))) AS NewHID,
charindex(',',reverse(left(hid,len(hid)-charindex(',',reverse(hid))))) AS NowRightMostCommaPosition,
right(left(hid,len(hid)-charindex(',',reverse(hid))),charindex(',',reverse(left(hid,len(hid)-charindex(',',reverse(hid)))))-1) AS Result
from #test
;with cte as
(
select * from #test
cross apply dbo.DelimitedSplit8K (hid,',')
)
select t1.Item from cte t1
inner join (select hid, max(itemnumber)-1 as MaxMin1 from cte group by hid) as t2
on t1.hid = t2.hid and t1.ItemNumber = t2.MaxMin1
drop table #test
First two off the top of my head, others may chip in with other ways.
Second you will need delimitedsplit8k from the "String Splitter" link in my signature.
November 17, 2015 at 8:35 am
Same as above but slightly different approach
create table #test(HID varchar (50))
insert into #test values
('12,3,16'),
('16,10,256'),
('2,150,110,200'),
('5,70,4'),
('2,100,110,150,200,123,159602,1568')
Select *
,charindex(',',reverse(hid)) as last_comma
,LEN(HID) as string_length
,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String
,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma
,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1) As endResult
FROm #test
November 17, 2015 at 8:37 am
Richie T (11/17/2015)
Same as above but slightly different approachcreate table #test(HID varchar (50))
insert into #test values
('12,3,16'),
('16,10,256'),
('2,150,110,200'),
('5,70,4'),
('2,100,110,150,200,123,159602,1568')
Select *
,charindex(',',reverse(hid)) as last_comma
,LEN(HID) as string_length
,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String
,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma
,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1) As endResult
FROm #test
You need a final reverse on the EndResult to get it back into the correct order
November 17, 2015 at 9:10 am
insert into #test values
('2100110'),
('2100110,12')
select
HID,
case when last_comma = 0 then HID
when next_to_last_comma = 0 then LEFT(HID, LEN(HID) - last_comma)
else SUBSTRING(HID, LEN(HID) - next_to_last_comma + 2, next_to_last_comma - last_comma - 1)
end as next_to_last_value
from #test
cross apply (
select charindex(',', reverse(HID)) as last_comma
) as assign_alias_names1
cross apply (
select charindex(',', reverse(HID), last_comma + 1) AS next_to_last_comma
) as assign_alias_names2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 17, 2015 at 9:37 am
Good eyes 😉
Select *
,charindex(',',reverse(hid)) as last_comma
,LEN(HID) as string_length
,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String
,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma
,reverse(SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1)) As endResult
FROm #test
November 17, 2015 at 2:07 pm
This is similar to the option previously posted by Anthony using the DelimitedSplit8k.
WITH CTE AS
(
SELECT *, MAX(ItemNumber) OVER(PARTITION BY hid) - 1 AS thisItem
FROM #test
CROSS APPLY dbo.DelimitedSplit8K (hid,',')
)
SELECT Item
FROM CTE
WHERE ItemNumber = thisItem;
If the maximum amount of items is 4, then a simpler approach is available.
SELECT HID, PARSENAME( REPLACE( HID, ',', '.'), 2)
FROM #test
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply