1

I am creating a view v_purchasereport and gathering information from the PO table (Purchaseorders) and from the NAME column I want only the alphabeths AND "-" included as in our country you can have two part names with a "-" in between. No numbers nor other characters to be included in the view's name column.

In the PO.name column it can be: Mike Matthews +8012345678 or Matt Phillips (+8012345678)

And I want the v_purchasereport.name result to be Mike Matthews or Matt Phillips

Hope you can help! Thanks in advance :)

J33
  • 13
  • 3
  • 1
    **Consumable** sample data, expect results, and, very **importantly**, *your* attempts will help us help you. T-SQL's forté, however, is not String Manipulation – Thom A Oct 27 '20 at 09:45
  • Do these answer your question? [How to strip all non-alphabetic characters from string in SQL Server?](https://stackoverflow.com/q/1007697/2029983), [How to remove non alphanumeric characters in SQL without creating a function?](https://stackoverflow.com/q/54953097/2029983). [T-SQL strip all non-alpha and non-numeric characters](https://stackoverflow.com/q/9636045/2029983) – Thom A Oct 27 '20 at 09:48

2 Answers2

1
DECLARE @name nvarchar(max)='Mike Matthews +8012345678'  

--DECLARE @name nvarchar(max)='Matt Phillips (+8012345678)'

SELECT
  CASE 
    WHEN CHARINDEX('(', @name) > 0
      THEN LTRIM(LEFT(@name, CHARINDEX('(', @name)-1))
    WHEN CHARINDEX('+', @name) >0
      THEN LTRIM(LEFT(@name, CHARINDEX('+', @name)-1))
    ELSE  @name 
  END AS name

Link: https://rextester.com/UEZHB46672

Rasel Ahmed
  • 86
  • 4
0

I would use:

select left(name, patindex('% [(+]%', name + ' (') - 1)

Here is a db<>fiddle.

You can easily add additional splitter characters in the pattern. Or simply use ' [^-a-zA-Z] if you wanted any non-alpha character.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786