We help IT Professionals succeed at work.

format a string

98 Views
Last Modified: 2015-10-22
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!
Comment
Watch Question

Deepak ChauhanSQL Server DBA
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"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.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Author

Commented:
Do NOT store it that way at all
I didn't store this data. it was giving like this =(
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
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
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
I guess your tomorrow is my night =)
My tomorrow just started it is 8:36am my time but I understand
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

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

Author

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
;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));
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
COHFL, do you still need help with this question?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.