nvarchar - int comparisions

Question

nvarchar - int comparisions

mtz676

SSCrazy Eights

Points: 9212
More actions
May 11, 2020 at 4:27 am

#3750353

I have an nvarchar column size which has values like 1200 MB
How do I compare in a where clause :
where size > 1000 in a select clause and get the relevant results
Cannot redefine column to int or any other datatype.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Not enough information here to give you any meaningful help. Do some values have "KB" or "GB" instead of "MB". What sort of comparisons do you want to do? You'll need to strip off the "MB" and then convert to int (or decimal if appropriate). If you do indeed have other units then you'll need CASE expressions to multiply by 1000.

John

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 2

Please post a repro with DDL for a table, some insert statements and then a query. Please format code using the "Insert/edit code sample" button in the toolbar.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 3

CREATE TABLE [dbo].[A]([DatabaseName] [nvarchar](1000) NULL, [DBSize] [nvarchar](1000) NULL) 
insert into A values (DatabaseA , 580960.00 MB)
insert into A values(DatabaseB, 23523.00 GB)
insert into A values(DatabaseC,234.00 KB)

Table values:

DatabaseA 580960.00 MB

DatabaseB 23523.00 GB

DatabaseC 234.00 KB

How do I return only the numbers excluding MB KB GB in a select clause

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

mtz676 wrote:

CREATE TABLE [dbo].[A]([DatabaseName] [nvarchar](1000) NULL, [DBSize] [nvarchar](1000) NULL) 
insert into A values (DatabaseA , 580960.00 MB)
insert into A values(DatabaseB, 23523.00 GB)
insert into A values(DatabaseC,234.00 KB)
Table values:
DatabaseA 580960.00 MB
DatabaseB 23523.00 GB
DatabaseC 234.00 KB
How do I return only the numbers excluding MB KB GB in a select clause

Your SQL inserts do not work. I'm not looking to fix your sample code.

Try this, maybe it will work

SELECT DatabaseName, DBSize = LEFT(DBSize, CHARINDEX(' ', DBSize))
FROM A

How to post data/code on a forum to get the best help.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

Basically, you need to find the end of the number. CHARINDEX() can help, looking for a space, but if your data is not consistent, this might be an issue. You could also look for a "B" and then subtract back to get to the number and substring that out.

Please fix DDL and inserts. We should be able to run code on our machine, which can't see your database, and reproduce a setup.

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 6

If the column DBSize is consistent then this may work

declare @SomeTable table
(
    DatabaseName nvarchar(1000) not null,
    DBSize nvarchar(1000) not null
);

insert into @SomeTable values('DatabaseA', '580960.00 KB');
insert into @SomeTable values('DatabaseB', '23523.00 MB');
insert into @SomeTable values('DatabaseC', '234.00 GB');

select a.DatabaseName,
    case
        when right(a.DBSize, 2) = 'GB' then
            cast(cast(left(a.DBSize, charindex(' ', a.DBSize)) as numeric) as int) * 1000000
        when right(a.DBSize, 2) = 'MB' then
            cast(cast(left(a.DBSize, charindex(' ', a.DBSize)) as numeric) as int) * 1000
        else
            cast(cast(left(a.DBSize, charindex(' ', a.DBSize)) as numeric) as int)
    end as DBSizeInKB
from @SomeTable as a
order by a.DBSize;

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Here's a simplification of Jonas' method above.

--===== Create the test table and populate it.
     -- This is NOT a part of the solution.
DECLARE @SomeTable TABLE
        (
         DatabaseName   NVARCHAR(1000)  NOT NULL
        ,DBSize         NVARCHAR(1000)  NOT NULL
        )
;
 INSERT INTO @SomeTable
        (DatabaseName,DBSize)
 VALUES  (N'DatabaseA', N'580960.00 KB')
        ,(N'DatabaseB', N'23523.00 MB')
        ,(N'DatabaseC', N'234.00 GB')
;
 SELECT * FROM @SomeTable
;
--===== Isolate the numeric disk size and set it to the common "MB" scale 
     -- for comparison purposes. (Proposed solution).
 SELECT  DatabaseName
        ,DBSize
        ,DBSizeMB   = CONVERT(DECIMAL(19,3),LEFT(DBSize,CHARINDEX(N' ',DBSize)))
                    * CASE RIGHT(DBSize,2) --Convert to MB for comparison purposes
                        WHEN N'GB' THEN 1024
                        WHEN N'MB' THEN 1
                        WHEN N'KB' THEN 0.0009765625 -- 1/2024.0
                        ELSE NULL
                      END
   FROM @SomeTable
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)