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)
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
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
Create Table #Temp
(
Code VarChar(20)
)
Insert Into #Temp(Code)
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 6 (of 6 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