Good day!

There is a need to break the line into components, namely how to pull out the phone number following the comma ('8555555555, 8999999999, 9166666666, etc.')

How to do it?

There can be one or fifteen phones, and you need to pull out all the phone numbers from the maximum possible after the comma))

My life:

SELECT '8555555555, 8999999999, 9166666666' as [All NUM] ,SUBSTRING('8555555555, 8999999999, 9166666666', 1, charindex(',', '8555555555, 8999999999, 9166666666')-1) as [First NUM] ,SUBSTRING('8555555555, 8999999999, 9166666666', charindex(',', '8555555555, 8999999999, 9166666666')+1, LEN('8555555555, 8999999999, 9166666666') - charindex(',', '8555555555, 8999999999, 9166666666')) as [All after first NUM] ,right('8555555555, 8999999999, 9166666666',charindex(',', SUBSTRING('8555555555, 8999999999, 9166666666', charindex(',', '8555555555, 8999999999, 9166666666')+1, LEN('8555555555, 8999999999, 9166666666') - charindex(',', '8555555555, 8999999999, 9166666666')))-2) as [Last NUM]

    2 answers 2

    Apply the SQL Server function string_split type:

      SELECT value FROM STRING_SPLIT('8555555555, 8999999999, 9166666666', ', ')
    • Thanks for the answer, and it is possible more fully, but as far as I understand, STRING_SPLIT converts the data into rows, and I need to columns. Or am I wrong? - Gaspar
    • one
      @ Yevgeny Kozlovsky The columns are not convenient, how to work with them, contact each individual individually and you should not forget that all columns should be described in the request, therefore if you write 15 columns in the request, what happens if the line is 16 phones - Mike
    • @Mike, yes, really, I will try to do it with one column, I did not think that I would need to contact everyone) Thank you! - Gaspar
    • At STRING_SPLIT second argument is single-character. - i-one
     select * ,ltrim(rtrim(substring(phone_nm, i2 + 1, i1 - i2 -1 ))) phone_nm_1 from ( select * ,charindex( ' ', x1.phone_nm + ' ', number ) i1 ,case when number = 0 then 0 else charindex( ' ', x1.phone_nm + ' ', number - 1 ) end i2 from t1 x1 inner join master.dbo.spt_values x2 on x2.type = 'P' and number between 0 and len( x1.phone_nm) ) x where i1 != i2

    Solved the issue in a different way.