none
SQL - help with selecting strings

    Question

  • Hi all,

    I've got a variable with a string like:

    'substring1_substring2_substring3_etcetera'

    Each substring is separated bij the character '_' and varies in length (substring1 and substring2 might differ in length, but also substring1 in row1 and substring1 in row2).

    How can I select each separated substring? Do I have to use combinations of substring and/ or charindex or is there a simpler way?

    Thanks!

    Saturday, September 07, 2013 3:27 PM

Answers

  • Assuming that the character '_' can appear 3 times at the max within the string. Refer below query,

    declare @strs nvarchar(200)
    set @strs = 'substring1_substring2_substring3_etcetera'
    select 
    parsename(replace(@strs,'_','.'),4),
    parsename(replace(@strs,'_','.'),3),
    parsename(replace(@strs,'_','.'),2),
    parsename(replace(@strs,'_','.'),1)


    Regards, RSingh

    Saturday, September 07, 2013 3:37 PM

All replies

  • You can simply use split function. For more info see this link:

    Split function in SQL Server to break Comma separated strings


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Saturday, September 07, 2013 3:30 PM
  • Assuming that the character '_' can appear 3 times at the max within the string. Refer below query,

    declare @strs nvarchar(200)
    set @strs = 'substring1_substring2_substring3_etcetera'
    select 
    parsename(replace(@strs,'_','.'),4),
    parsename(replace(@strs,'_','.'),3),
    parsename(replace(@strs,'_','.'),2),
    parsename(replace(@strs,'_','.'),1)


    Regards, RSingh

    Saturday, September 07, 2013 3:37 PM
  • Hi ,

    Try like this

    DECLARE @String NVARCHAR(MAX)= 'substring1_substring2_substring3_etcetera'
    SELECT PARSENAME (REPLACE(@String,'_','.'),4) ,
    PARSENAME (REPLACE(@String,'_','.'),3) ,
    PARSENAME (REPLACE(@String,'_','.'),2) ,
    PARSENAME (REPLACE(@String,'_','.'),1)

    Also try this link - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-Split-a-308206f3

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, September 07, 2013 3:42 PM
    Moderator
  • There are many kludges for this design error. PARSENAME() is one, but there are many others.

    The right answer is that you need to learn what First Normal Form (1NF), the foundation of RDBMS. If each of the substrings has meaning in and of itself, then it is an attribute value. It should be in its own column, with  constraints, etc. 

    Find the guy that did this to you and expect to correct most of his  code. 



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

    • Proposed as answer by Saeid Hasani Saturday, September 07, 2013 7:09 PM
    Saturday, September 07, 2013 7:00 PM