CODEWITHSUNDEEP
sqlsql-servert-sql
Gopal
Gopal

Reputation: 11992

Hot to compare the string value from left to right

How to compare the string value from left to right

Table1 (Column name: Reference)

A123BD
E-.D256
123-D14:

I have a reference number in table1, I want to compare if the table 1 reference number is matching with user input from left to right. example User may input like A123BD398202 then it should compare with table1 coumn, here left to right value of user input is matching with table 1 column A123BD 

user input

A123BD124
E-.D257809
A123-D14:2345

' for the above user input expected Output is

Matching (left to right string of table1 column is matching with user input)
Not Matching (left to right string of table1 column is not matching with user input)
Not matching (left to right string of table1 column is not matching with user input)

Also string is not a fixed length, user input compare the table 1 column string from left to right.

How to achieve this in SQL query. Can any one help me

Upvotes: 0

Views: 222

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9480

Simple like may work for you. See following example.

declare @refs table(reference varchar(50))
insert @refs values
('A123BD'),
('E-.D256'),
('123-D14:')

declare @userInput table (ui varchar(50))
insert @userInput values
('A123BD124'),
('E-.D257809'),
('A123-D14:2345')

select ui,
   case when exists(select 1 from @refs where ui like reference+'%') then 'match'
   else 'not match' end isMatch
from @userInput

Output:

ui               isMatch
A123BD124        match
E-.D257809       not match
A123-D14:2345    not match

Upvotes: 2

Sean
Sean

Reputation: 492

looks like you want when one string is in the other, but nit vice versa. You can check the length of the column and check the left X characters of the input. see example below in tsql.

  declare @input varchar(50) = 'A123-D14:2345'

/*
A123BD124
E-.D257809
A123-D14:2345
*/

if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (Column1 varchar(50))


Insert into #table1 (Column1)
Values('A123BD')
        ,('E-.D256')
        ,( '123-D14:')

Select 
    Column1
    ,Left(Column1, Len(Column1))
    ,@input
    ,Len(column1)
    ,Len(@input)
    ,Case when Left(@input, Len(Column1)) = Column1 then 'Matched'
        Else 'unmatched'
        END as [IsMatched]
from
#table1

Upvotes: 0

Related Questions