May 14, 2014 at 5:11 am
when i use right
RIGHT("Class",20)
just pulls back class
May 14, 2014 at 5:12 am
ronan.healy (5/14/2014)
when i use rightRIGHT("Class",20)
just pulls back class
Did you read the documentation I linked to?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 5:17 am
no cant see any link in any of the posts
May 14, 2014 at 5:23 am
ronan.healy (5/14/2014)
no cant see any link in any of the posts
Yeah, the fonts for URLs are really subtle.
You need to hover over the functions I mentioned with your mouse.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 8:02 am
i can get this to work as all the string lenghts a re different
tried this as well still wont work
SUBSTRING(Group,1,LEN(Group) - FINDSTRING(REVERSE(Group),"-",1) - 1)
is there anything else than can be done
May 15, 2014 at 12:44 am
ronan.healy (5/14/2014)
i can get this to work as all the string lenghts a re differenttried this as well still wont work
SUBSTRING(Group,1,LEN(Group) - FINDSTRING(REVERSE(Group),"-",1) - 1)
is there anything else than can be done
Sample data and desired output? In your origingal strings from your question, the character "-" is not present.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 15, 2014 at 2:33 am
ya i no i swaped it for a full stop as there is one in it.
so file comes in like this
Net asset value per share
Net Asset Value for Ignis Absolute Return Government Bond Fund . Class A GBP
Total Outstanding shares for Ignis Absolute Return Government Bond Fund . Class A GBP
Net Asset Value per share for Ignis Absolute Return Government Bond Fund . Class A GBP
Net Asset Value for Ignis Absolute Return Government Bond Fund. Class I2 GBP
Total Outstanding shares for Ignis Absolute Return Government Bond Fund. Class I2 GBP
Net Asset Value per share for Ignis Absolute Return Government Bond Fund. Class I2 GBP
Net Asset Value for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged
Net Asset Value for Ignis Absolute return Government Bond Fund. Class A EUR Hedged
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class A EUR Hedged
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class A EUR Hedged
Net Asset Value for Ignis Absolute return Government Bond Fund. Class A CHF
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class A CHF
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class A CHF
Net Asset Value for Ignis Absolute return Government Bond Fund. Class I2 CHF
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 CHF
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 CHF
want the out put like this
Class A GBP
Class A GBP
Class A GBP
Class I2 GBP
Class I2 GBP
etc
May 15, 2014 at 2:51 am
;with cte as
(
SELECT 'Net asset value per share' AS ID
UNION ALL SELECT 'Net Asset Value for Ignis Absolute Return Government Bond Fund . Class A GBP'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute Return Government Bond Fund . Class A GBP'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute Return Government Bond Fund . Class A GBP'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute Return Government Bond Fund. Class I2 GBP'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute Return Government Bond Fund. Class I2 GBP'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute Return Government Bond Fund. Class I2 GBP'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class A EUR Hedged'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class A EUR Hedged'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class A EUR Hedged'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class A CHF'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class A CHF'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class A CHF'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class I2 CHF'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 CHF'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2'
)
SELECT RIGHT(ID,CASE WHEN CHARINDEX('.',REVERSE(ID))= 0 THEN 0 ELSE CHARINDEX('.',REVERSE(ID))-1 END) FROM cte
Regards,
Mitesh OSwal
+918698619998
May 15, 2014 at 3:01 am
hi
could you explain that to me no idea what it means or how to go about doing it
May 15, 2014 at 3:10 am
--Load the string in table varible
DECLARE @tbl TABLE
(
String NVARCHAR(1000)
)
INSERT INTO @tbl
SELECT 'Net asset value per share' AS ID
UNION ALL SELECT 'Net Asset Value for Ignis Absolute Return Government Bond Fund . Class A GBP'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute Return Government Bond Fund . Class A GBP'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute Return Government Bond Fund . Class A GBP'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute Return Government Bond Fund. Class I2 GBP'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute Return Government Bond Fund. Class I2 GBP'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute Return Government Bond Fund. Class I2 GBP'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class A EUR Hedged'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class A EUR Hedged'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class A EUR Hedged'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class A CHF'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class A CHF'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class A CHF'
UNION ALL SELECT 'Net Asset Value for Ignis Absolute return Government Bond Fund. Class I2 CHF'
UNION ALL SELECT 'Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 CHF'
UNION ALL SELECT 'Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2'
The Below step read the "." character from right and start reading the words from there till the end
SELECT RIGHT(String,
CASE WHEN CHARINDEX('.',REVERSE(String))= 0
THEN 0
ELSE CHARINDEX('.',REVERSE(String))-1
END
)
FROM @tbl
Regards,
Mitesh OSwal
+918698619998
May 15, 2014 at 3:21 am
for f sake. some of the columns dont have full stops. this data is all over the place
can i use that to copy it from the word class instead of the "." like "class" would that work
also cheers for explaining that it works perfectly only for this data
May 15, 2014 at 3:26 am
have tried it like this
SELECT RIGHT(String,
CASE WHEN CHARINDEX('class',REVERSE(String))= 0
THEN 0
ELSE CHARINDEX('class',REVERSE(String))-1
END
)
FROM @tbl
returns blank
May 19, 2014 at 2:53 am
SELECT RIGHT(String,
CASE WHEN CHARINDEX('ssalc',REVERSE(String))= 0
THEN 0
ELSE CHARINDEX('ssalc',REVERSE(String))+5
END
)
FROM @tbl
Regards,
Mitesh OSwal
+918698619998
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply