CODEWITHSUNDEEP
sql-server
Emma
Emma

Reputation: 562

Len not working when there are spaces in data

Using the code below is not working as there are spaces in Column 2. e.g. 026923102690542625548030 (1)
The spaces are needed in between the number and (1) but I believe there is also training spaces which can be stripped out.

   select RIGHT([Column 2], len([Column 2]) - 19) AS newcol2, [Column 2] from [dbo].[231]

This is returning nothing in newcol2 when I am trying to show 48030 (1)

I've tried replacing len with DATALENGTH and that just gives me a duplicate of [Column 2]

*** added with more information

When I try 

    select 
RIGHT([Column 2], len(REPLACE([Column 2],' ','')) - 2) AS newcol2, 
[Column 2] from [dbo].[231]

I get the result

newcol2      [Column 2]
(1)          026923102690970425568331 (1)                     
             026923102690970525568331

I need the newcol2 column to say 68331 (1) and 68331 The field types are nvarchar(50)

Tried the following

    select 
RIGHT([Column 2], len(REPLACE([Column 2],CHAR(10),'')) - 19) AS newcol2, 
[Column 2] from [dbo].[231]

    select 
RIGHT([Column 2], len(REPLACE([Column 2],CHAR(13),'')) - 19) AS newcol2, 
[Column 2] from [dbo].[231]

still the same

*** Added with solution

    WITH CTE
AS
(
    SELECT 
        [Column 2] = LTRIM(RTRIM(REPLACE(REPLACE([Column 2],CHAR(10),''),CHAR(13),'')))
        FROM [dbo].[231]
)
SELECT
     RIGHT([Column 2], len([Column 2]) - 19) AS newcol2,
    [Column 2]
    FROM CTE

Upvotes: 1

Views: 122

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

try length after replacing the space

select 
RIGHT([Column 2], len(REPLACE([Column 2],' ','')) - 19) AS newcol2, 
[Column 2] from [dbo].[231]

OR

select 
    RIGHT([Column 2], len(REPLACE([Column 2],' (1)','(1)')) - 19) AS newcol2, 
    [Column 2] from [dbo].[231]

I'm getting the right answer when I ran this

DECLARE @T VARCHAR(50)='026923102690542625548030 (1)'

SELECT
    RIGHT(@T, LEN(@T) - 19) AS newcol2,
    @T

Try Replacing CHAR(10) and CHAR(13) from the column and calculate len. that might work. like this

WITH CTE
AS
(
    SELECT 
        [Column 2] = LTRIM(RTRIM(REPLACE(REPLACE([Column 2],CHAR(10),''),CHAR(13),'')))
        FROM [dbo].[231]
)
SELECT
    RIGHT([Column 2], len(REPLACE([Column 2],' ','')) - 19) AS newcol2, 
    [Column 2]
    FROM CTE

Upvotes: 1

Related Questions