August 2, 2018 at 5:56 pm
I have standard data as
C1588-27-3
C1588-28-3
C1588-29-8
I want result as in SQL as
1588-27
1588-28
1588-29
I tried replacefirst character REPLACE('C1588-27-3','C','') it works then i want to usesubstring so try to find CHARINDEX
it always give me 0 so try with
CHARINDEX giving 0 instead of 6 characters
SELECT PATINDEX('%-%', 'C1588-27-3'), CHARINDEX('C1588-27-3','-')
August 2, 2018 at 7:42 pm
Drop Table If Exists #Temp
Create Table #Temp
(
Code VarChar(20)
)
Insert Into #Temp(Code) August 2, 2018 at 8:42 pm
Thanks it worked like charm!!!
August 2, 2018 at 10:03 pm
sks_989 - Thursday, August 2, 2018 5:56 PMI have standard data asC1588-27-3
C1588-28-3
C1588-29-8I want result as in SQL as
1588-27
1588-28
1588-29I tried replacefirst character REPLACE('C1588-27-3','C','') it works then i want to usesubstring so try to find CHARINDEX
it always give me 0 so try with PATINDEX that works but it give mefirst occurrence i want second occurrence is any idea how this will work. Appreciateyour help.='font-variant-ligatures:>CHARINDEX giving 0 instead of 6 characters
SELECT PATINDEX('%-%', 'C1588-27-3'), CHARINDEX('C1588-27-3','-')
Is the format and segment lengths of the data always the same?
😎
August 3, 2018 at 5:31 am
andycadley - Thursday, August 2, 2018 7:42 PMDrop Table If Exists #Temp
Insert Into #Temp(Code)
Create Table #Temp
(
Code VarChar(20)
)
Values
('C1588-27-3'),
('C1588-28-3'),
('C1588-29-8')Select Code, SubString(Code, 2, SecondHypen - 2)
From #Temp
Cross Apply (Select CharIndex('-', Code, CharIndex('-', Code) + 1) As SecondHypen) S
This will work only if you 2nd occurrence of string
Drop Table If Exists #Temp
Create Table #Temp
(
Code VarChar(20)
)
Insert Into #Temp(Code)
Values
('C1588-27-3'),
('C1588-28-3'),
('C1588-29-8');
Solution:
select substring(Code,1,(charindex('-',code)+charindex('-',reverse(code))))
from #Temp;
Saravanan
August 3, 2018 at 9:32 am
Select Code, new_string
FROM #Temp
CROSS APPLY (
SELECT STUFF(Code, 1, 1, '') AS base_string
) AS ca1
CROSS APPLY (
SELECT LEFT(base_string, CHARINDEX('-', base_string,
CHARINDEX('-', base_string) + 1) - 1) AS new_string
) AS ca2
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".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply