How base string function use in SQL-CodePudding

I have column named Batch No with some specific data like this

TEST1234 - 2022-02-15
100 - 2022-02-15
RO-001 - 2022-02-08
3232 - 2022-02-01
N/A
N/A
MJNK44 - 2021-04-24
MJNK44 - 2021-04-24
N/A
N/A
N/A
564235 - 2021-03-18

I want 3232 value in a separate column and 2022-02-01 value in other column how can I do that ?

CodePudding user response：

You can look for the position of the dash with CHARINDEX or PATINDEX.
And use that position to get the substring.

And since the date is always the last 10 characters, just use RIGHT to get it.

SELECT *
, NULLIF(SUBSTRING(BatchNo, 0, CHARINDEX(' -', Batchno)),'') as BatchName
, TRY_CONVERT(DATE, RIGHT(BatchNo,10)) as BatchDate
FROM Your_Table

CodePudding user response：

Check this out, have used one of the rows for testing, replace t with your table name, and remove the WITH line.

WITH t as (select 'TEST1234 - 2022-02-15' as val)
select t.*,
       LEFT(val, charindex('-', val) - 1), RIGHT(val, LEN(val) - charindex('-', val))
from t;