Trim a part of a String

  • A+
Category:Languages

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.

output:

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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: