Trim a part of a String

Question

I have a table with a column which is long. I need to take only the name from the column.

This is the column name:

QSTY-IOM-HFVNBJGYG | Mobile #: 9876541323 | CUSTOMER NAME: MNOP UNIPOUYTREA POIUY | INVOICE DATE:02/28/18 | EMP#: 101466 | EMPLOYEE NAME: ANGELINA CASIANO

I just need to pick up the customer name from this column. I tried Charindex and it was displying an error message of

Invalid length parameter passed to the LEFT or SUBSTRING function.

output:

MNOP UNIPOUYTREA POIUY

"I tried Charindex" may we see that attempt? It is probably the correct way. — Hans Keﬆing, May 15 '18 at 10:05
select substring (Description, charindex('|',Description,1)+1, charindex(' ',Description,1)-charindex('|',Description,1) ),* from #Final — Looking_for_answers, May 15 '18 at 10:14

Zohar Peled · Accepted Answer · 2018-05-15T10:45:27.277

Since you are using SQL Server 2016, you can use it's built in STRING_SPLIT method to convert your string to a table. Then all you have to do is to select the value from the table that starts with CUSTOMER NAME:, like this:

DECLARE @s varchar(300) = 'QSTY-IOM-HFVNBJGYG | Mobile #: 9876541323 | CUSTOMER NAME: MNOP UNIPOUYTREA POIUY | INVOICE DATE:02/28/18 | EMP#: 101466 | EMPLOYEE NAME: ANGELINA CASIANO'

SELECT [value]
FROM STRING_SPLIT(@s, '|')
WHERE LTRIM([value]) LIKE 'CUSTOMER NAME:%'

However, you should read Is storing a delimited list in a database column really that bad? and normalize your database if possible.

Shnugo · Answer 2 · 2018-05-16T14:35:03.093

With SQL-Serer-2016+ you should use STRING_SPLIT.

With lower versions I'd use this approach:

A quite straight forward combination of XQuery and T-SQL

DECLARE @string VARCHAR(1000)='QSTY-IOM-HFVNBJGYG | Mobile #: 9876541323 | CUSTOMER NAME: MNOP UNIPOUYTREA POIUY | INVOICE DATE:02/28/18 | EMP#: 101466 | EMPLOYEE NAME: ANGELINA CASIANO';

WITH Casted AS
(
    SELECT CAST('<x>' + REPLACE((SELECT @string AS [*] FOR XML PATH('')),' | ','</x><x>') + '</x>' AS XML)
          .query('/x[substring(text()[1],1,13)="CUSTOMER NAME"]')
          .value('(/x/text())[1]','varchar(1000)') AS TheFragment
)
SELECT LTRIM(RTRIM(SUBSTRING(TheFragment,CHARINDEX(':',TheFragment)+2,1000)))
FROM Casted;

The CTE will transform your string to an XML, which allows to read each part separately. The .query() will return the one fragment starting with "CUSTOMER NAME" while .value() takes the (complete) text out of this fragment. The rest is easy SUBSTRING...

score 2 · Answer 3 · edited May 15 '18 at 10:27

2

If the format is consistent, you can try like below:

declare @val varchar(max) = 'QSTY-IOM-HFVNBJGYG | Mobile #: 9876541323 | CUSTOMER NAME: MNOP UNIPOUYTREA POIUY | INVOICE DATE:02/28/18 | EMP#: 101466 | EMPLOYEE NAME: ANGELINA CASIANO'

select substring(@val, charindex('CUSTOMER NAME:', @val) + 15, charindex('INVOICE DATE:', @val) - (charindex('CUSTOMER NAME:', @val) + 18))

edited May 15 '18 at 10:27

PSK

17,547
5
32
43

answered May 15 '18 at 10:24

Partha

75
1
1
11

this is the error: Invalid length parameter passed to the LEFT or SUBSTRING function – Looking_for_answers May 15 '18 at 11:06
Does all your strings have the same format as the string stored in the @val variable? – Partha May 15 '18 at 11:10
No they keep changing depending on the length of the name – Looking_for_answers May 15 '18 at 11:17
That is understandable. But what I meant is whether there is any difference in the overall structure of strings.. – Partha May 15 '18 at 11:22
@Looking_for_answers what is meant is: is "CUSTOMER NAME" always immediately followed by "INVOICE DATE", like in this sample, or can the fragments be in any order? – Hans Keﬆing May 15 '18 at 11:25

Gordon Linoff · Answer 4 · 2018-05-15T10:45:31.237

Here is a way that looks for CUSTOMER NAME:

with params as (
      select ' CUSTOMER NAME: ' as custname
     )
select t.*,
       (case when t.col like '%' + custname + '%'
             then s2.s2 
        end) as customer_name
from params cross join
     t outer apply
     (select substring(t.col, charindex(custname, col + custname), len(col))as s1
     ) s1 outer apply
     (select stuff(left(s1.s1, charindex('|', s1.s1 + '|') - 2),
                   1, len(custname) + 1, ''
                  ) as s2
     ) s2;

This should work if customer_name is at the end of the string, regardless of the order of the values, and even if 'CUSTOMER NAME' is not in the string at all.

Of course, if you are using SQL Server 2016, then a solution using split_string() is better.

Trim a part of a String

4 Answers4