March 9, 2017 at 5:43 am
Hi,
Need one small help in getting two values from a string value using substring. Here is the scenario
DECLARE @Source VARCHAR(100)
Set @Source ='model://CMDTY/SRC_Name'
so from @Source variable i need to retrieve CMDTY and SRC_Name value separately, but in some case i receive @Source variable as:
Set @Source ='model://CMDTY/SRC_Name/MID'
Set @Source ='model://CMDTY/SRC_Name/MID/Deploy'
so in both cases the value should always be CMDTY and SRC_Name. Please let me know how to use substring for variable length of inputs and to retrieve from specific position only.
Thanks
Sam
March 9, 2017 at 6:05 am
Perhaps I am missing something, but the position of both CMDTY and SRV_Name are in the same position in your example, so the following will always work:SELECT SUBSTRING(@Source, 9,5) AS CMDTY, SUBSTRING(@Source, 15,8) AS Srv_Name;
Could you, perhaps, give us some examples showing you actual needs, with varying requirements please?
Thanks
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 9, 2017 at 6:28 am
Here CMDTY and SRC_Name are just an example. And it can vary in length.
For instance for SRC_Name i need to retrieve the value after first occurance of '/' in the variable @source.
Hope i made it clear. Let me know if there are confusions.
Here are some real examples of source values:
'model://MONEY/GBPCHF.3Y.BG0L.125'
'model://INDEX/IMAREX.PANAMAX'
'model://INDEX_New/JIBAR.Z2AR.12M1/LAST'
'model://FXG_LBMA/EU.ME.LBMA.A3G_USD.FXG/PRICE'
-- Here is the output i needed
Col1 COL2
FXG_LBMA EU.ME.LBMA.A3G_USD.FXG
INDEX IMAREX.PANAMAX
INDEX_New JIBAR.Z2AR.12M1
MONEY GBPCHF.3Y.BG0L.125
March 9, 2017 at 7:06 am
Search this site for "splitter functions". You can use a splitter function with "/" as the delimiter.
John
March 9, 2017 at 7:18 am
Using Substring:USE DevTestDB;
GO
CREATE TABLE #Source (String varchar(255));
GO
INSERT INTO #Source
VALUES
('model://MONEY/GBPCHF.3Y.BG0L.125'),
('model://INDEX/IMAREX.PANAMAX'),
('model://INDEX_New/JIBAR.Z2AR.12M1/LAST'),
('model://FXG_LBMA/EU.ME.LBMA.A3G_USD.FXG/PRICE');
GO
SELECT *
FROM #Source;
SELECT SUBSTRING(String + '/',9, CHARINDEX('/',String,9)-9) AS CMDTY,
SUBSTRING(String + '/', CHARINDEX('/',String + '/',9) +1, CHARINDEX('/',String + '/',CHARINDEX('/',String + '/',9)+1) - CHARINDEX('/',String + '/',9) - 1) AS [Srv_Name]
FROM #Source;
GO
DROP TABLE #Source;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 9, 2017 at 10:29 pm
Thank you so much Thom. Your solution works...
Viewing 6 posts - 1 through 5 (of 5 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