locked
Query to find data that consist of exactly the same alphabet and number of alphabet in database? RRS feed

  • Question

  • User1124379756 posted

    Hi guys, 

    Just like what the title says, i would like to create a query to find data that consist of exactly the same alphabet and number of alphabet in database?

    example: 

    string myString = oynes

    [MyDatabase] has "NoYes" 

    Then the result should return exist. 

    Any one has an idea on this? any help is greatly appreciated. Thanks :)

    Tuesday, March 1, 2016 5:34 AM

Answers

  • User-698989805 posted

    Hello Malfolozy! Use the following:

    DECLARE @va varchar(20) = 'AT-2015'
    SELECT CAST(CASE WHEN LEN(ColName) = LEN(@va) THEN
            'Exist'
               ELSE
            'No exist' END AS varchar) AS Existence
    FROM TableName

    I've modified the query. Please check the below out:

    DECLARE @var1 VARCHAR(MAX)
    DECLARE @var2 VARCHAR(MAX)
    DECLARE @c CHAR(1)
    DECLARE @i INT
    DECLARE @var3 VARCHAR(100) = ''
    DECLARE @var4 VARCHAR(100) = ''
    
    SET @var1 = 'oynes'
    SET @var2 = 'NoYes'
    
    WHILE DATALENGTH(@var1) > 0
    BEGIN
        SET @c = Left(@var1, 1)
        SET @var1 = SUBSTRING(@var1, 2, LEN(@var1))
        SET @i = 1
    
        WHILE @i <= LEN(@var3)
            AND @c > SUBSTRING(@var3, @i, 1)
            SET @i += 1
        SET @var3 = left(@var3, @i - 1) + @c + SUBSTRING(@var3, @i, LEN(@var3))
    END
    
    WHILE DATALENGTH(@var2) > 0
    BEGIN
        SET @c = Left(@var2, 1)
        SET @var2 = SUBSTRING(@var2, 2, LEN(@var2))
        SET @i = 1
    
        WHILE @i <= LEN(@var4)
            AND @c > SUBSTRING(@var4, @i, 1)
            SET @i += 1
        SET @var4 = left(@var4, @i - 1) + @c + SUBSTRING(@var4, @i, LEN(@var4))
    END
    
    SELECT CASE WHEN @var3 = @var4 THEN 
       'Exist'
     ELSE 
       'Not Exist'
    END
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 5:54 AM

All replies

  • User-698989805 posted

    Hello Malfolozy! Use the following:

    DECLARE @va varchar(20) = 'AT-2015'
    SELECT CAST(CASE WHEN LEN(ColName) = LEN(@va) THEN
            'Exist'
               ELSE
            'No exist' END AS varchar) AS Existence
    FROM TableName

    I've modified the query. Please check the below out:

    DECLARE @var1 VARCHAR(MAX)
    DECLARE @var2 VARCHAR(MAX)
    DECLARE @c CHAR(1)
    DECLARE @i INT
    DECLARE @var3 VARCHAR(100) = ''
    DECLARE @var4 VARCHAR(100) = ''
    
    SET @var1 = 'oynes'
    SET @var2 = 'NoYes'
    
    WHILE DATALENGTH(@var1) > 0
    BEGIN
        SET @c = Left(@var1, 1)
        SET @var1 = SUBSTRING(@var1, 2, LEN(@var1))
        SET @i = 1
    
        WHILE @i <= LEN(@var3)
            AND @c > SUBSTRING(@var3, @i, 1)
            SET @i += 1
        SET @var3 = left(@var3, @i - 1) + @c + SUBSTRING(@var3, @i, LEN(@var3))
    END
    
    WHILE DATALENGTH(@var2) > 0
    BEGIN
        SET @c = Left(@var2, 1)
        SET @var2 = SUBSTRING(@var2, 2, LEN(@var2))
        SET @i = 1
    
        WHILE @i <= LEN(@var4)
            AND @c > SUBSTRING(@var4, @i, 1)
            SET @i += 1
        SET @var4 = left(@var4, @i - 1) + @c + SUBSTRING(@var4, @i, LEN(@var4))
    END
    
    SELECT CASE WHEN @var3 = @var4 THEN 
       'Exist'
     ELSE 
       'Not Exist'
    END
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 5:54 AM
  • User-62323503 posted
    create function udf_SortString(@val varchar(100))
    returns varchar(100)
    as
    begin
    	declare @len int, @ctr int 
    	set @len=len(@val)
    	set @ctr=1
    	declare @tab table(chr char(1)) 
    	while @ctr<=@len
    	begin 
    		insert into @tab
    		select lower(SUBSTRING(@val, @ctr, 1))
    		set @ctr=@ctr+1
    	end
    	return (select chr+'' from @tab order by ascii(chr) for xml path(''))
    end
    
    declare @val varchar(100)
    set @val = 'oynes'
    set @val= dbo.udf_SortString(@val)
    if exists(select 1 from tablename where dbo.udf_SortString(colname)=@val)
    	select 'exist'
    else
    	select 'Not exist'	
    

    Tuesday, March 1, 2016 6:25 AM