Function

Question

User-1499457942 posted

Hi

  Is there any function to extract all characters before any Special Character or space is found. In below case it should return 1165613YN

1165613YN -/Y. 

Thanks

Answer 1

User753101303 posted

Hi,

You could try something based on https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017 as for example :

SELECT LEFT(data,PATINDEX('%[ -]%',data)-1)
FROM
-- A way to get sample data without creating an actual table
(VALUES('1165613YN -/Y'),('AAA-B')) AS t(data)

PATINDEX is used to get the position of the first space or - character and then LEFT is used to get characters up to before this position.

Answer 2

User-1499457942 posted

Hi Patrice

  It is not only space or - . It can be any other special character also.

Thanks

Answer 3

User753101303 posted

 You could add them the list and use [ -!/+] etc... or if you look at the doc for PATINDEX you have also an explanation about possible wildcards such as https://docs.microsoft.com/en-us/sql/t-sql/language-elements/wildcard-character-s-not-to-match-transact-sql?view=sql-server-2017 which would allow to define them as any character not being a to z or 0 to 9 (you would use then something such as [^a-z0-9]

Of course you have often to understand and adapt the provided code which is IMO muich better than just expecting to copy/paste code without getting how it works.

For example maybe :

SELECT LEFT(data,PATINDEX('%[^a-z0-9]%',data)-1)
FROM
-- A way to get sample data without creating an actual table
(VALUES('1165613YN -/Y'),('AAA-B'),('CC!DD')) AS t(data)

Edit: and of course you still have to possibly deal with cases where no special characters at all is found your data (is this possible ?) maybe with a CASE statement.