pulling data from end of column

  • when i use right

    RIGHT("Class",20)

    just pulls back class

  • ronan.healy (5/14/2014)


    when i use right

    RIGHT("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

  • no cant see any link in any of the posts

  • 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

  • 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

  • ronan.healy (5/14/2014)


    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

    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

  • 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

  • ;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

  • hi

    could you explain that to me no idea what it means or how to go about doing it

  • --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

  • 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

  • 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

  • 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