Post reply

Need Help seperating data in SQL

  • October 12, 2009 at 2:25 pm

    #205446

    Hi

    I am new to SQL and just need some help with the following. I have data in a column such as:-

    BMW 320 48 month

    Mazda 323 petrol 24mnth

    I need to take out the number of months from this column, and put them in a seperate one. There are 6 ways in which the months are diplayed:-

    48 Month

    24 Month

    36 Month

    48mnths

    24mnths

    36mnths

    At the end I want to have the car model and contract months in 2 seperate columns.

    Thank you

    Any help is much appreciated.

  • October 12, 2009 at 2:38 pm

    #1064931

    DECLARE @A varchar(100)

    SET @A = 'BMW 320 48 month'

    SELECT @A

    SELECT LEFT(RTRIM(REPLACE(REPLACE(@A,'Month',''),'mnths','')),

    LEN(RTRIM(REPLACE(REPLACE(@A,'Month',''),'mnths',''))) - 2) CarModel,

    RIGHT(RTRIM(REPLACE(REPLACE(@A,'Month',''),'mnths','')),2) ContractMonths

    Replace @A with the name of your column.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 12, 2009 at 2:59 pm

    #1064941

    string manipulation can be a bit tricky. I think Garadin has you on the right track for part of it.

    To pull out the car, you need some rules about how this is set up. Are there always the spaces between them? Are there ever spaces inside a name, like "Monte Carlo"

    Basically you need to search inside the string. CHARINDEX and PATINDEX help you find the place in the string and then SUBSTRING will let you pull out parts of the string based on the positions inside the string.

  • October 13, 2009 at 6:03 am

    #1065144

    Hi

    Thank you for your help

    Sorry I forgot to menshion that some of the contract periods are in brackets

    e.g. BMW M3 SMG (36mnths)

    I need some way of deleting the contract lenghts from the end of each string or splitting them into two different columns.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply