Issue with a substring statement 1

[nathanharcup]

Hi All,
I have a table that looks like the below:-

Dell Inc Optiplex 740
Dell Inc. Optiplex 740
Dell Inc. Optiplex 740 eNHANCED

I want to split it into two as per the below :-
Col1 Col2
Dell Optiplex 740
Dell Optiplex 740
Dell Optiplex 740 eNHANCED

I have run the below:-
SELECT LEFT(MAKEANDMODEL, 4) AS Make,substring(MAKEANDMODEL, charindex('DELL', MAKEANDMODEL) + 9, charindex('DELL', MAKEANDMODEL) - (charindex('DELL', MAKEANDMODEL) - 100)) as model
FROM dbo.Table_1

It sort of works but i end up with the below:-
Col1 Col2
Dell Optiplex 740
Dell Optiplex 740
Dell Optiplex 740 eNHANCED

Is there a way to ensure that there is not a space before the values in col2?

Thanks

Nathan

 

[gmmastros]

Maybe you could use LTrim.

Ex:

SELECT  LEFT(MAKEANDMODEL, 4) AS Make,
        [!]LTrim([/!]substring(MAKEANDMODEL, charindex('DELL', MAKEANDMODEL) + 9, charindex('DELL', MAKEANDMODEL) - (charindex('DELL', MAKEANDMODEL) - 100))[!])[/!] as model
FROM         dbo.Table_1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nathanharcup]

thats fantastic

Thanks