Extract 2 fields from 1

Question

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;

Answer 1

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

Answer 2

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

Answer 3

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/