SQL to update a number to 3 decimal places between the character "."

I am using SQL 2012

I want to convert numbers to 3 decimal places for each number between the character ".".  For example:
1.1.5.2 -> 001.001.005.002
1.2 -> 001.002
4.0 -> 004.000
4.3 ->004.003
4.10 -> 004.010

How can I update these?
maverick0728Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Maverick,

It looks like you're trying to reformat an IP address.  A word of caution about that -- many interfaces will treat a number with a leading zero as an octal value.  If you format an IP address with leading zeros there's an excellent chance that the address will be converted to the wrong value.

10.10.10.10  may not be the same as 010.010.010.010 (which could be treated as 8.8.8.8).


Kent
0
maverick0728Author Commented:
I am not trying to format an IP address.  It's and ItemNo in an engineering bill of materials.
0
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  It's a little wordy, but it works.  :)

  SELECT right ('000' + cast (int_value as varchar(3)), 3)

Do that for every value that you want to write in a fixed length.


Good Luck!
Kent
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

maverick0728Author Commented:
Kent,
Thanks for the reply.  Unfortunately that query syntax does not produce the desired result.
needs to be:
1.1.5.2 -> 001.001.005.002
1.2 -> 001.002
4.0 -> 004.000
4.3 ->004.003
4.10 -> 004.010
0
Kent OlsenData Warehouse Architect / DBACommented:
Can you post your query?
0
maverick0728Author Commented:
SELECT ItemNo
FROM EstAsmTemp

Open in new window

0
maverick0728Author Commented:
Attached is some sample data from this query.
itemno.txt
0
Kent OlsenData Warehouse Architect / DBACommented:
Ahha. My mistake.  Didn't realize that the item number was a single string.

There are several approaches to this.  One is a UDF to parse the string and return the values delimited by the period.  Another is a recursive query to do the same thing.  Another is an external program to convert the strings and leave the results in a form that can be used for the UPDATE (this is a very short C program).

My personal choice would be a recursive query.

This sample shows how to get the desired results.

WITH d AS
(
  SELECT '0.0' as text
  UNION ALL 
  SELECT '1.1.1.2'
  UNION ALL
  SELECT '1.1.4'
  UNION ALL
  SELECT '1.1.4.1.4'
  UNION ALL
  SELECT '2.7.2'
),
q AS
(
  SELECT cast (right ('000' +
           case when charindex ('.', TEXT) = 0 then text
                else LEFT (text, charindex ('.', text) - 1) 
           end, 3) as varchar) as newstring,
         case when charindex ('.', TEXT) = 0 then NULL
              else RIGHT (text, len (text) - charindex ('.', text))
         end as text,
         text as original
  FROM d
  union all
  SELECT cast (newstring + '.' +
         case when charindex ('.', TEXT) = 0 then RIGHT ('000' + text, 3)
              else RIGHT ('000' + LEFT (text, charindex ('.', text) - 1), 3)
         end as varchar) as newstring,
         case when charindex ('.', TEXT) = 0 then NULL
              else RIGHT (text, len(text) - charindex ('.', text))
         end as text,
         original
  FROM q
  WHERE TEXT is not null             
)
select * from q
order by original, newstring

Open in new window


Note that this kind of query also prints out intermediate results that we don't care about.  It's easy to spot the rows that we want -- text is NULL.  So we just filter the results with 'WHERE text is NULL'.

That query will have to be modified slightly to replace the sample data with itemno from you table.  It'll look something like this:

WITH
q AS
(
  SELECT cast (right ('000' +
           case when charindex ('.', Itemno) = 0 then Itemno
                else LEFT (Itemno, charindex ('.', Itemno) - 1) 
           end, 3) as varchar) as newstring,
         case when charindex ('.', Itemno) = 0 then NULL
              else RIGHT (Itemno, len (text) - charindex ('.', Itemno))
         end as text,
         text as original
  FROM {mytable}
  union all
  SELECT cast (newstring + '.' +
         case when charindex ('.', TEXT) = 0 then RIGHT ('000' + text, 3)
              else RIGHT ('000' + LEFT (text, charindex ('.', text) - 1), 3)
         end as varchar) as newstring,
         case when charindex ('.', TEXT) = 0 then NULL
              else RIGHT (text, len(text) - charindex ('.', text))
         end as text,
         original
  FROM q
  WHERE TEXT is not null             
)
select * from q
WHERE text is NULL
order by original, newstring

Open in new window


Finally, we'll want to do the actual update:

WITH
q AS
(
  SELECT cast (right ('000' +
           case when charindex ('.', Itemno) = 0 then Itemno
                else LEFT (Itemno, charindex ('.', Itemno) - 1)
           end, 3) as varchar) as newstring,
         case when charindex ('.', Itemno) = 0 then NULL
              else RIGHT (Itemno, len (text) - charindex ('.', Itemno))
         end as text,
         text as original
  FROM {mytable}
  union all
  SELECT cast (newstring + '.' +
         case when charindex ('.', TEXT) = 0 then RIGHT ('000' + text, 3)
              else RIGHT ('000' + LEFT (text, charindex ('.', text) - 1), 3)
         end as varchar) as newstring,
         case when charindex ('.', TEXT) = 0 then NULL
              else RIGHT (text, len(text) - charindex ('.', text))
         end as text,
         original
  FROM q
  WHERE TEXT is not null            
)
UPDATE {mytable}
SET ItemNo = q.text
FROM q
INNER JOIN {mytable} old
  ON old.itemno = q.newstring
WHERE q.text is NULL;

As with any SQL, I suggest that you test it before you apply it to a critical table!

Good Luck,
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
I have to admit, I'd use CROSS APPLYs here rather than recursion, since I'd expect a known, finite number of levels, and I think the CA code is easier to follow:


SELECT
    ItemNo,
    RIGHT('000' +  SUBSTRING(ItemNo, 1, dot1 - 1), 3) +
    ISNULL('.' + RIGHT('000' +  SUBSTRING(ItemNo, dot1 + 1, ISNULL(dot2 - dot1, 3) - 1), 3), '') +
    ISNULL('.' + RIGHT('000' +  SUBSTRING(ItemNo, dot2 + 1, ISNULL(dot3 - dot2, 3) - 1), 3), '') +
    ISNULL('.' + RIGHT('000' +  SUBSTRING(ItemNo, dot3 + 1, ISNULL(dot4 - dot3, 3) - 1), 3), '') +
    ISNULL('.' + RIGHT('000' +  SUBSTRING(ItemNo, dot4 + 1, ISNULL(dot5 - dot4, 3) - 1), 3), '') +
    ISNULL('.' + RIGHT('000' +  SUBSTRING(ItemNo, dot5 + 1, ISNULL(dot6 - dot5, 3) - 1), 3), '') +
    ISNULL('.' + RIGHT('000' +  SUBSTRING(ItemNo, dot6 + 1, ISNULL(dot7 - dot6, 3) - 1), 3), '') AS Modified_ItemNo
FROM (
    SELECT '1.1.4.1.3' AS ItemNo UNION ALL
    SELECT '1.1.4.1.4' UNION ALL
    SELECT '1.23.456.78'    
) AS test_data
CROSS APPLY (
    SELECT CHARINDEX('.', ItemNo) AS dot1
) AS dot1
CROSS APPLY (
    SELECT NULLIF(CHARINDEX('.', ItemNo, dot1 + 1), 0) AS dot2
) AS dot2
CROSS APPLY (
    SELECT NULLIF(CHARINDEX('.', ItemNo, dot2 + 1), 0) AS dot3
) AS dot3
CROSS APPLY (
    SELECT NULLIF(CHARINDEX('.', ItemNo, dot3 + 1), 0) AS dot4
) AS dot4
CROSS APPLY (
    SELECT NULLIF(CHARINDEX('.', ItemNo, dot4 + 1), 0) AS dot5
) AS dot5
CROSS APPLY (
    SELECT NULLIF(CHARINDEX('.', ItemNo, dot5 + 1), 0) AS dot6
) AS dot6
CROSS APPLY (
    SELECT NULLIF(CHARINDEX('.', ItemNo, dot6 + 1), 0) AS dot7
) AS dot7
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.