1

I want to remove .0 from value of '123.0'.

Actually value of $.Item2 is '123.0' .I want to convert it to '123' removing .0

I tried as below but didn't worked

 SELECT Item1,Item2,Item3
 FROM OPENJSON( @DataTable  )
 WITH (Item1 NVARCHAR(100) '$.Item1'
        , Item2 NVARCHAR(100) LEFT('$.Item2', CHARINDEX('.',  '$.Item2') - 1) 
        , Item3 NVARCHAR(100) '$.Item3'    
        );

Kindly suggest.

Sample Data:

  • Item1: '234-00945'

  • Item2: '7512345671195.0'

  • Item3: '5027501.0'

Shnugo
  • 66,100
  • 9
  • 53
  • 114
chandra sekhar
  • 1,093
  • 4
  • 14
  • 27

3 Answers3

1

You could try using a cast as INT 

         SELECT CAST (your_value AS INT)
         ....

of if you need string 

         SELECT CAST(CAST(your_value AS INT)  AS  NVARCHAR(100))
         ....
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
1

As your values are alphanumerical codes, any approach with a cast to a number type will fail.

This is my suggestion:

DECLARE @YourJSON NVARCHAR(MAX)=N'{"Item1":"234-00945","Item2":"7512345671195.0","Item3":"5027501.0"}';

--We use a CASE to test the final 2 characters and cut them away, if needed:

 SELECT CASE WHEN RIGHT(Item1,2)='.0' THEN SUBSTRING(Item1,1,LEN(Item1)-2) ELSE Item1 END AS Item1
       ,CASE WHEN RIGHT(Item2,2)='.0' THEN SUBSTRING(Item2,1,LEN(Item2)-2) ELSE Item2 END AS Item2
       ,CASE WHEN RIGHT(Item3,2)='.0' THEN SUBSTRING(Item3,1,LEN(Item3)-2) ELSE Item3 END AS Item3
 FROM OPENJSON( @YourJSON  )
   WITH (Item1 NVARCHAR(100) '$.Item1'
        ,Item2 NVARCHAR(100) '$.Item2'
        ,Item3 NVARCHAR(100) '$.Item3'    
        );

Any attempt with CAST will lead to errors.
Using TRY_CAST would work, but might cut away something like .123 too.
And something like 123.6 would lead to a round-up and come back as 124.

Hint

In general it is very important to stick to the right type. Your codes are strings, although some of them look like numbers...

UDATE: The same but without the repeating CASE expression

You can use the following (or create an UDF):

 SELECT p.*
 FROM
 (
 SELECT Cleaned.*
 FROM OPENJSON( @YourJSON  ) TheJsonItems
 CROSS APPLY(SELECT TheJsonItems.[key]
                   ,CASE WHEN RIGHT(TheJsonItems.[value],2)='.0' 
                         THEN SUBSTRING(TheJsonItems.[value],1,LEN(TheJsonItems.[value])-2) 
                         ELSE TheJsonItems.[value] END) Cleaned(ItemName,ItemValue)
 ) t
 PIVOT(MAX(t.ItemValue) FOR t.ItemName IN(Item1,Item2,Item3)) p
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

Instead of removing it from charindex, since this value is decimal so directly convert it into bigint as of your value.

 SELECT Item1,
case when Item2 like '%.0' then 
substring(Item2 , 1 , PATINDEX('%[^0-9]%', Item2) - 1) else Item2  end as Item2,
Item3    --- cast over here
 FROM OPENJSON( @DataTable  )
 WITH (Item1 NVARCHAR(100) '$.Item1'
        , Item2 NVARCHAR(100) '$.Item2'
        , Item3 NVARCHAR(100) '$.Item3'    
        );


For example---

Declare @query varchar(max) 

set @query = '7512345671195.00'

select substring(@query, 1 , PATINDEX('%[^0-9]%', @query)-1)


Result
-------
7512345671195
DarkRob
  • 3,843
  • 1
  • 10
  • 27
  • I'm getting error " The conversion of the nvarchar value '7512345671195.0' overflowed an int column. " – chandra sekhar Aug 27 '19 at 07:35
  • @chandrasekhar: I've updated my ans use `bigint` instead of `int`. – DarkRob Aug 27 '19 at 07:36
  • Another error "Error converting data type nvarchar to bigint." – chandra sekhar Aug 27 '19 at 07:37
  • PATINDEX didn't worked.Getting values as only '7' instead of '7512345671195' – chandra sekhar Aug 27 '19 at 07:47
  • I am not sure how this didn;t work added example in my ans, please check again, might be because previously I writed `item2` in single quote `'`, updated my ans, check again now – DarkRob Aug 27 '19 at 07:51
  • It worked but I have another issue. Item2 may have value as "234-00945" instead of "7512345671195.0" . So my requirement is, if Item2 have any type value either int or nvarchar , I want to remove .0 from value if it exists. – chandra sekhar Aug 27 '19 at 07:55
  • PATINDEX returning "234-00945" as just "234".It want to have value as "234-00945" – chandra sekhar Aug 27 '19 at 07:56
  • updated my ans with case, as you needed only if `.0` will come in the last then it will remove '.0' else give exact value. – DarkRob Aug 27 '19 at 07:59