format a string

first check by selecting

select LEFT(LicenceNO , CHARINDEX('-', LicenceNO ) - 1)+'-'+convert(varchar(4),DATEPART(yyyy, getdate()))
from Table
WHERE CHARINDEX('-', LicenceNO ) > 0

Select allOpen in new window

Then if you are good with result.

UPDATE Table
SET LicenceNO = LEFT(LicenceNO , CHARINDEX('-', LicenceNO ) - 1)+'-'+convert(varchar(4),DATEPART(yyyy, getdate()))
WHERE CHARINDEX('-', LicenceNO ) > 0

Select allOpen in new window

I cant do getdate because the year portion already comes on my column.
123456-01-2009
123456-2014
654321-2009
987654-2008
123456-15
654123-13
.... and so on
always the last digits dictates the year

>>"What's the best practices to achieve this?"
well you asked:
Do NOT store it that way at all, use 2 separate columns, each with an appropriate data type.

However I'll come back with a query suggestion for the single column shortly.

Please try this:

select
      T.*
      , case when CA1.pos > 0 then left(T.LicenseNumber, CA1.pos-1) else T.LicenseNumber end
        + '-'
        + case when len(CA3.YearPart) =2 then '20' + CA3.YearPart
               else CA3.YearPart
          end as CombinedParts
from YourTable T
cross apply (
  select charindex('-', T.LicenseNumber)
  ) CA1 (pos)
cross apply (
  select case when CA1.pos > 0 then substring(T.LicenseNumber, CA1.pos+1, 50) end -- 50 is an example,  use max length of field
  ) CA2 (DatePart)
cross apply (
  select case when charindex('-',CA2.DatePart) > 0 then substring(CA2.DatePart, charindex('-',CA2.DatePart)+1, 50) else CA2.DatePart end
  ) CA3 (YearPart)
  

Select allOpen in new window

nb: I use CROSS APPLYs so that I can reuse the alias in subsequent calculations

Do NOT store it that way at all

I didn't store this data. it was giving like this =(

Regardless of who made it that way, the best practice is to not keep it that way. Sorry, it's not personal, just a technical observation.

Perhaps it can be fixed one day.

Thanks, I will keep in mind.

I have try the query you have provided but there is one instance where the combine parts don't work =(
000376-042015
I guess there is data where a second "-" is missing or they did not entered

It's not a pattern in your sample data, but it doesn't surprise me that exceptions exist.

It's too late for my brain to tackle now, best I can offer is to attempt it tomorrow.

I guess your tomorrow is my night =)
My tomorrow just started it is 8:36am my time but I understand

Yep. It's 23:37 for me.

I was able to fix it though so don't worry. This is what I did

case when CA1.pos > 0 then left(T.LicenseNumber, CA1.pos-1) else T.LicenseNumber end
        + '-'
        + case when len(CA3.YearPart) =2 then '20' + CA3.YearPart
               else case when len(CA3.YearPart) =6 then right(ca3.YearPart,4) else CA3.YearPart end
          end

Select allOpen in new window

;with cte as
(select part1 + case when len(part2) >= 4 then right(part,4)
                     else '20' + right(part2,2)
                end as formattedlicense from
 (select left(licenseNo,charindex('-',licenseNo)) as part1,
  substring(licenseNo,charindex('-',licenseNo)+1,100) as part2
  from yourtable))
update yourtable t set t.licenseNo = cte.formattedlicense
where left(t.licenseNo,charindex('-',licenseNo))
    = left(cte.formattedlicense,charindex('-',formattedlicense));

COHFL, do you still need help with this question?