Share via

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,601 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Naomi 7,366 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
  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
  3. Yitzhak Khabinsky 25,731 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