sql server中char数据类型列的最大条件 [英] Max condition for a char datatype column in sql server

问题描述

我有一个包含char数据类型的表。需要从该列中获取最大数量。



该列中的某些值为1,2,3，A，B


我只得到char - '''''，如何获得数值中的最大值？

I have a table with char datatype for a column. Need to fetch max number from that column.

some values in that column are 1,2,3,A,B

while fetching max(column) I only get the char - ''B'', how do i get the max among numeric value?

select max(cast(floor as int)) from floormaster

推荐答案

只需使用：





从floorMaster中选择MAX（楼层），其中ISNUMERIC（楼层）= 1

Just use this:


select MAX(floor) from floorMaster where ISNUMERIC(floor)=1

试试这个

Hi ,

Try this

-- Split Function
IF OBJECT_ID('fnSplit') IS NOT NULL DROP FUNCTION dbo.fnSplit
GO
CREATE FUNCTION dbo.fnSplit(
    @InputString VARCHAR(8000), -- List of delimited items
    @Delimiter CHAR(1) = ',') -- delimiter that separates items)
RETURNS @List TABLE (ColumnValues VARCHAR(8000))
AS
BEGIN
    DECLARE @NextString NVARCHAR(40), @Pos INT

    SET @InputString = @InputString + @Delimiter
    SET @Pos = CHARINDEX(@Delimiter,@InputString)

    WHILE (@pos <> 0)
    BEGIN
	   SET @NextString = SUBSTRING(@InputString,1,@Pos - 1)
	   INSERT INTO @List(ColumnValues) SELECT @NextString 
	   SET @InputString = SUBSTRING(@InputString,@pos+1,len(@InputString))
	   SET @pos = CHARINDEX(@Delimiter,@InputString)
    END 
    RETURN 
END
GO
-- Single Value 
DECLARE @Values VARCHAR(100)='1,2,3,A,B,34,40'
SELECT MAX(ColumnValues) FROM fnSplit(@Values,',') WHERE ISNUMERIC(ColumnValues)=1
-- For Tables
SELECT (SELECT MAX(ColumnValues) FROM fnSplit(Column_Name,',') WHERE ISNUMERIC(ColumnValues)=1)
FROM Table_Name

您可以使用交叉应用和自定义拆分字符串函数来实现此目的。

You can use cross apply and a custom split string function to achieve that.

CREATE FUNCTION  [dbo].[SplitString]
(
	@stringToSplit VARCHAR(MAX)
)
RETURNS @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
	DECLARE @name NVARCHAR(255)
	DECLARE @pos INT
	
	WHILE CHARINDEX(',', @stringToSplit) > 0
	BEGIN
	    SELECT @pos = CHARINDEX(',', @stringToSplit)  
	    SELECT @name = SUBSTRING(@stringToSplit, 1, @pos -1)
	    
	    INSERT INTO @returnList
	    SELECT @name
	    
	    SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) -@pos)
	END
	
	INSERT INTO @returnList
	SELECT @stringToSplit
	
	RETURN
END

create table floormaster(floor char(10));
insert floormaster(floor) values('1,2,3,A,B');
insert floormaster(floor) values('5,7,3,A,B'); 
select max(Name) from floormaster f cross apply dbo.SplitString(f.floor) where ISNUMERIC(Name) = 1;

结果为7

The result will be 7

这篇关于sql server中char数据类型列的最大条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！