1

I want to lookup column for Russian numbers stored in separate table. The Russian numbers are made up of string having multiple letters.

The problem is when i use any string function it fails because the Russian number is made up of multiple word.

I want to check whole string not only any appearance.

I have rule which checks if any column as only integer. here i am checking for Russian integer. If Rule passes it will throw error "Contains Only Digits"

For Example:

Name

пять83e094
пять83e094
семьvnala

Russian Numbers Russian

 один
два
три
четыре
пять
шесть
семь
восемь
девять
десять
ноль
сто
тысяча
NULL
NULL
миллиард
NULL
миллион

How to do it??

Solution:

        `select n'один' Name into #TEMP union
    select n'два'                  union
    select n'три'                  union
    select n'четыре'               union
    select n'пять'                 union
    select n'шесть'                union
    select n'семь'                 union
    select n'восемь'               union
    select n'девять'               union
    select n'десять'               union
    select n'ноль'                 union
    select n'сто'                  union
    select n'тысяча'               union
    select n'NULL'                 union
    select n'NULL'                 union
    select n'миллиард'             union
    select n'NULL'                 union
    select n'миллион' 

    select n'пятьвосемь' Country into #TEMP1 union
    select n'india   '   union
    select n'japanone'  union
    select n'нольмиллиард'  union
    select n'fourone    '   union
    select n'europe '

    DECLARE
    @MAX_CNT NUMERIC,
    @MIN_CNT NUMERIC,
    @WORD_LEN_CNT NUMERIC,
    @WORD_CNT NUMERIC,
    @COUNTRY NVARCHAR(50),
    @STRING NVARCHAR(50),
    @STRING_BUFFER NVARCHAR(50),
    @IF_CNT NUMERIC;
    SET @MIN_CNT=1;
    SET @WORD_CNT=1;
    SET @STRING='';
    SET @COUNTRY='';

    BEGIN


    select @MAX_CNT =(select MAX(SEQ) Coun from (
    SELECT ROW_NUMBER()OVER(ORDER BY COUNTRY)SEQ,*  FROM #TEMP1
    )xx)


    ---------------------WHILE LOOP-----------------------------------------

    WHILE(@MIN_CNT<=@MAX_CNT)
    BEGIN
    --PRINT 1
        SELECT @COUNTRY=(select country from (
        SELECT ROW_NUMBER()OVER(ORDER BY COUNTRY)SEQ,*  FROM #TEMP1
        )xx WHERE SEQ=@MIN_CNT ) 

    --print @COUNTRY
    --PRINT @COUNTRY
    -----------------CHECKING THE WORD WHETHER NUMBER OR NOT---------------------------
        SET @WORD_LEN_CNT=LEN(@COUNTRY);
        SET @WORD_CNT=1
        SET @STRING_BUFFER=''

    -----------------SECOND WHILE LOOP-------------------------------------------------

    WHILE @WORD_CNT<=@WORD_LEN_CNT
    BEGIN
    --PRINT 2
        SELECT @STRING=SUBSTRING(@COUNTRY,@WORD_CNT,1)

        SET @STRING_BUFFER=@STRING_BUFFER+@STRING
        SET @WORD_CNT=@WORD_CNT+1
        --print @STRING_BUFFER

    -----------------CHECKING THE NUMBER PRESENT IN BASE TABLE------------------------

    SELECT @IF_CNT=COUNT(1) FROM #TEMP WHERE NAME=@STRING_BUFFER
    --print @IF_CNT
    IF @IF_CNT=1
    BEGIN

        SET @STRING_BUFFER=''
    END

    end
    IF @STRING_BUFFER='' 
    BEGIN
    --PRINT 3
    --print @COUNtry 
        PRINT 'Contains Digits'END

    SET @MIN_CNT=@MIN_CNT+1;
    end
    end'
vijay damale
  • 21
  • 7
  • Regex can be a solution.Check this : https://study.com/academy/lesson/regular-expressions-in-sql-server-databases-implementation-use.html – Amira Bedhiafi May 07 '19 at 14:12
  • 1
    Hi and welcome to SO. I don't understand the question here. What are you trying to do? See [here](https://stackoverflow.com/help/how-to-ask) for ideas on how to ask a good question. – Sean Lange May 07 '19 at 14:18
  • 1
    @vijadamale . . . What results do you want? That should be in the question. – Gordon Linoff May 07 '19 at 14:22
  • I have rule which checks if any column as only integer. here i am checking for Russian integer. If Rule passes it will throw error "Contains Only Digits" – vijay damale May 08 '19 at 07:31
  • @AmiraBedhiafi i have already used it. But the problem is i want to check character by character in string. Also, Russian integer is made up of multiple characters – vijay damale May 08 '19 at 07:33
  • Check this. It may help you : https://stackoverflow.com/questions/18325680/sql-server-2012-express-do-not-understand-russian-letters – Amira Bedhiafi May 08 '19 at 07:35
  • @vijaydamale: If you fixed the issue you should either post an answer to your question, or delete it. – 500 - Internal Server Error May 10 '19 at 08:14
  • I have posted the solution in question as i am unaware how to post code as answer. – vijay damale May 10 '19 at 09:56

0 Answers0