CODEWITHSUNDEEP
sqlsql-server-2008
Alpha2k
Alpha2k

Reputation: 2241

Removing spaces in complex names

I am trying to insert complex names like Juan Carlos but I want to remove all the spaces except the one between Juan and Carlos. Lets use # as space to see spaces better.

When inserting I have tried RTRIM(LTRIM(@Name)) however It seems not to work, I tried to insert ###Jua#Car### but when I select the field with DATALENGTH([Name]) I get the lenght of 14.

As I see that string I can count 13 characters, not 14.

1. What is the character I cannot count?
2. How can I end up getting Juan#Carlos removing all the spaces if LTRIM and RTRIM does not work?

Update with more info:

The column datatype is nvarchar(100)
I just tried REPLACE([Name], ' ','') and the lenght i get is 12

Upvotes: 0

Views: 146

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can trim non-alphanumeric characters using a somewhat complicated method:

select t2.name2
from t outer apply
     (select (case when name like '%[a-zA-Z0-9]%'
                   then stuff(t.name, 1, patindex(t.name, '%[a-zA-Z0-9]%'), '')
                   else ''
              end) as name1
     ) t1 outer apply
     (select (case when name1 like '%[a-zA-Z0-9]%'
                   then left(t1.name1,
                             len(t1.name1) - patindex(reverse(t.name), '%[a-zA-Z0-9.]%')
                            )
                   else ''
              end) as name2
     ) t2

Upvotes: 1

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

Try to use in following, select separately FirstName, LastName and concat them with space:

DECLARE @FullName VARCHAR(MAX) = '  Juan Carlos  '

SELECT LEN(SUBSTRING(LTRIM(RTRIM(@FullName)), 1, CHARINDEX(' ', LTRIM(RTRIM(@FullName))) - 1) + ' ' +
       REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(@FullName))), 1, 
               CHARINDEX(' ', REVERSE(LTRIM(RTRIM(@FullName)))) - 1) )) AS [Len]

It returning len = 11

Upvotes: 0

Related Questions