如何在一个SQL查询中修剪，转换和比较SQL DB中的字符串 [英] How to trim, convert and compare a string from an SQL DB, in one SQL Query

问题描述

我想从类型为varchar的数据库中检索数据，例如
大小:100mm，100，20mm，500，450mm并从字符串中修剪"mm"，然后将其转换为整数，然后从同一列进行比较:例如，@ Size1< = size_column和@ size2> = size_column :

这是我要修剪和转换的查询，但这给了我错误.

谢谢你的时间......

I would like to retrieve data from a database which is of type varchar e.g
size: 100mm,100 ,20mm, 500, 450mm and trim ''mm'' from the string and than convert it to integer and than compare from from the same column: e.g @Size1 <= size_column and @size2 >= size_column:

here''s my query to trim and convert but it''s giving me errors.

thank u for your time......

SELECT DEVICE_NO,
CASE [Rms_Size]
WHEN
(SELECT RMS_SIZE FROM DW_DATA.DBO.DIM_DEVICE WHERE
Ltrim(Rtrim(Rms_Size)) LIKE '%mm')
THEN
(SELECT SUBSTRING(Rms_Size,1,LEN(rms_size)-2)
FROM DW_DATA.DBO.DIM_DEVICE
where Ltrim(Rtrim(Rms_Size)) LIKE '%mm')
ELSE
RMS_SIZE
END
AS RMS_SIZE
FROM
DW_DATA.DBO.DIM_DEVICE

推荐答案

尝试:

SELECT 
   DEVICE_NO,
   CASE 
      WHEN
         LTRIM(RTRIM(Rms_Size)) LIKE '%mm'
      THEN
         SUBSTRING(Rms_Size,1,LEN(rms_size)-2)
      ELSE
         RMS_SIZE
      END
      AS RMS_SIZE
FROM DW_DATA.DBO.DIM_DEVICE

这篇关于如何在一个SQL查询中修剪，转换和比较SQL DB中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！