How to split a string that appears twice?

Question

I have a table temp with columnname NAME and data like this:

NAME
-------------
sumansuman
nehaneha
anjalianjali

I want to output like this:

NAME
-------------
suman 
neha
anjali

Answer 1

If you are certain that each name is duplicated exactly then this should work

SELECT LEFT(name, LEN(name)/2) FROM temp

To catch rows where that is not the case:

SELECT name FROM temp
WHERE NOT name = LEFT(name, LEN(name)/2) + LEFT(name, LEN(name)/2)

Answer 2

try something like this,

DECLARE @MyTable TABLE(NAME VARCHAR(100))

INSERT INTO @MyTable
VALUES
     ('sumansuman')
    ,('nehaneha')
    ,('anjalianjali')
    ,('suman')
    ,('nehaanjali')

SELECT CASE WHEN SUBSTRING(NAME,1,len(NAME)/2) = SUBSTRING(NAME,(len(NAME)/2)+1,len(NAME)) 
                THEN SUBSTRING(NAME,1,len(NAME)/2) 
            ELSE NAME
        END
FROM @MyTable

the result is:

Output
------
suman
neha
anjali
suman
nehaanjali

Answer 3

Use a CTE to make the query both more efficient (only perform LEFT and LEN once per row) and more readable:

with CTE as (
    select name, LEFT(name, LEN(name)/2) half from temp
)
select case name when half + half then half
  else name end as name
from CTE

If you want only the doubled names rectified:

with CTE as (
    select name, LEFT(name, LEN(name)/2) half from temp
)
select half as name
from CTE
where name + half + half