How to compare right and left side of a string in SQL-CodePudding

I developed a query where I enter a column with names. The idea is that if the name and last name are the same, the column indicator should put a 'fail' and if not, should enter a pass

SELECT TOP 1000
name,
case when CHARINDEX(' ',=name)<>0 
and LEFT(TRIM(=name),CHARINDEX(' ',=name)-1)=RIGHT(TRIM(=name),CHARINDEX(' ',c=name) 1) then 'fail' 
else 'pass' end AS indicator
FROM Table

I have an entry in the column name that is N/A N/A and I think it should fail with the case I wrote but is giving me a 'pass' I am not sure what could be the problem here

CodePudding user response：

This worked for me, splitting like I usually do it. Although names may not have only one space in them.

with tbl(name) as (
select 'Darth Vader' union all
select 'N/A N/A' union all
select 'joe mama'
)
SELECT name,
case 
  when SUBSTRING(name,0,CHARINDEX(' ',name)) = SUBSTRING(name,CHARINDEX(' ',name) 1,LEN(name)) then  'fail' 
  else 
    'pass' 
  end AS indicator
FROM tbl;

CodePudding user response：

I think you can just write something simple like this:

CREATE TABLE #TEMP_NAME 
(
    FIRSTNAME VARCHAR(50), 
    LASTNAME VARCHAR(50)
)

INSERT INTO #TEMP_NAME
SELECT 'FIRST', 'FIRST'
SELECT 'LAST', 'FIRST'

SELECT 
        CASE 
            WHEN  PN.FIRSTNAME = PN.LASTNAME THEN 'FAIL'
            WHEN  PN.FIRSTNAME <> PN.LASTNAME THEN 'PASS'
        END AS NAME_PASS_FAIL   
FROM #TEMP_NAME PN

query result