Avatar of COHFL
COHFL
Flag for United States of America asked on

format a string

Hi there,
I have a column called license Number with a set or record that looks like this:
123456-MM-YYYY
123456-YY
123456-YYYY

and I need to format the column like this:
123456-YYYY

What's the best practices to achieve this?
Thanks!
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Deepak Chauhan

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

Open 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

Open in new window

COHFL

ASKER
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
PortletPaul

>>"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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PortletPaul

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)
  

Open in new window

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

ASKER
Do NOT store it that way at all
I didn't store this data. it was giving like this =(
PortletPaul

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
COHFL

ASKER
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
PortletPaul

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

ASKER
I guess your tomorrow is my night =)
My tomorrow just started it is 8:36am my time but I understand
Your help has saved me hundreds of hours of internet surfing.
fblack61
PortletPaul

Yep. It's 23:37 for me.
COHFL

ASKER
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

Open in new window

ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
awking00

;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));
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

COHFL, do you still need help with this question?