Thank you in advance.
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.
MNOP UNIPOUYTREA POIUY
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.