possible to use 'set' keyword in a case/when/else block? -- for parsing firstN/Middle/LastN


  • Here is my pseudo code.  Not working.  What I am trying to do is to abbreviate having to write a bunch of charindex(substring...)... lines   by doing the following

    Declare @s varchar(10), @i1 int, @i2 int
    set @s = 'a b c'
    select @s,
    case when charindex(' ', substring(@s, charindex(' ', @s) + 1, len(@s) - charindex(' ', @s))) > 0 then
    set @i = charindex(' ', substring(@s, charindex(' ', @s) + 1, len(@s) - charindex(' ', @s)))
     else substring(@s, charindex(' ', @s) + 1, len(@s) - charindex(' ', @s)) end

    Is there a way to do this?  or a correct way or a better way to parse words in a string?  or should I just write my own UDF?


    Rich P

    Wednesday, April 24, 2013 6:41 PM


All replies

  • Take a look at this blog post that suggests a way of doing this in series of steps by using CROSS APPLY technique

    Parsing the FullName field to individual components

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    Wednesday, April 24, 2013 6:42 PM
  • Thank you.  I did end up writing my own UDF.  It's not as fancy as the one suggested, but sort of does what I need.

    Rich P

    Wednesday, April 24, 2013 8:37 PM
  • It might be a better idea to do this kind of processing in your font end application, and pass normalized data to the database / stored procedure.


    Wednesday, April 24, 2013 10:25 PM
  • The foundation of RDBMS is First Normal Form (1NF), which has scalar values drawn from domains, which support theta operators, in the  unordered columns of unordered rows of unordered tables in a schema. 

    A good SQL programmer would never do parsing in SQL, just as a good baker does not butcher meat on his bread board. Wrong tool, wrong place. I am sure that someone will give you a kludge and you can avoid begin a good SQL programmer a little longer. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 25, 2013 1:26 AM
  • CASE expression returns a scalar value.

    It would be a good enhancement to introduce CASE WHEN THEN PROCESS option.

    Kalman Toth Database & OLAP Architect
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, May 02, 2013 9:21 AM