Function RRS feed

  • Question

  • User-1499457942 posted


      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. 


    Wednesday, December 12, 2018 8:46 AM

All replies

  • User753101303 posted


    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)
    -- 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.

    Wednesday, December 12, 2018 9:17 AM
  • User-1499457942 posted

    Hi Patrice

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


    Wednesday, December 12, 2018 9:39 AM
  • 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)
    -- 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.

    Wednesday, December 12, 2018 10:32 AM