Extract 2 fields from 1

david chase 41 Reputation points
2022-03-16T14:16:27.61+00:00

We ran across a query that has code similar to below to extract a vehicle make and vehicle model from a string. Even though it works it seems confusing. Any help to simplify is appreciated.

DECLARE @MakeAndModel varchar(80);

SET @MakeAndModel = 'BMW X3M COMPETITION ';

SELECT LEN(RTRIM(LTRIM(LEFT(@MakeAndModel, IIF(CHARINDEX(' ', @MakeAndModel) > 0, CHARINDEX(' ', @MakeAndModel) - 1,LEN(RTRIM(@MakeAndModel))))))) AS TextLen
        ,RIGHT(@MakeAndModel,LEN(@MakeAndModel) - LEN(RTRIM(LTRIM(LEFT(@MakeAndModel, IIF(CHARINDEX(' ', @MakeAndModel) > 0, CHARINDEX(' ', @MakeAndModel) - 1,LEN(RTRIM(@MakeAndModel)))))))) AS NewModel;
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-03-16T15:12:02.8+00:00

    I like to use multiple step approaches with self documenting names for each step, so the code will become clearer. The technique is demonstrated in this blog post
    https://blogs.lessthandot.com/index.php/datamgmt/datadesign/parsing-fullname-field-to-individual/

    0 comments No comments

  2. Tom Cooper 8,466 Reputation points
    2022-03-16T15:42:28.823+00:00

    One way

    ;With cteFirstSpace As
    (Select @MakeAndModel As MakeAndModel, CHARINDEX(' ', @MakeAndModel) As FirstSpace)
    
    Select Case When FirstSpace = 0 Then Len(MakeAndModel) Else FirstSpace - 1 End As TextLen,
      Case When FirstSpace = 0 Then '' Else SubString(MakeAndModel, FirstSpace+1, Len(MakeAndModel)) End As NewModel 
    From cteFirstSpace;
    

    Tom

    0 comments No comments

  3. Yitzhak Khabinsky 25,206 Reputation points
    2022-03-16T16:14:59.3+00:00

    Hi @david chase ,

    It is better to use tokenization instead of string parsing.

    We are converting an input string into XML data type, and using simple XQuery to get Make and Model.

    The XML looks like the following:

    <root>  
      <r>BMW</r>  
      <r>X3M</r>  
      <r>COMPETITION</r>  
      <r />  
    </root>  
    

    After that we are retrieving 1st token as a Make (...r[1]...), everything else after it (...r[position() gt 1]...) is a Model.

    T-SQL

    DECLARE @MakeAndModel VARCHAR(80) = 'BMW X3M COMPETITION '  
     , @separator CHAR(1) = SPACE(1);;  
          
    DECLARE @xml_vehicle XML = TRY_CAST('<root><r>' +   
          REPLACE(@MakeAndModel, @separator, '</r><r><') +   
          '</r></root>' AS XML);  
      
    SELECT @MakeAndModel AS MakeAndModel  
     , @xml_vehicle.value('(/root/r[1]/text())[1]', 'VARCHAR(30)') AS Make  
     , @xml_vehicle.query('data(/root/r[position() gt 1])').value('.', 'VARCHAR(30)')AS Model  
    

    Output

    +----------------------+------+-----------------+  
    |     MakeAndModel     | Make |      Model      |  
    +----------------------+------+-----------------+  
    | BMW X3M COMPETITION  | BMW  | X3M COMPETITION |  
    +----------------------+------+-----------------+  
    
    0 comments No comments