Geek Answers Handbook

T-SQL: how to get the exact string length in characters?

I am creating T-SQL SELECT statements for tables for which I do not have data type information up. In these instructions, I need to perform row manipulation operations, which depend on the length of the original value of the table columns.

One example (but not the only one) is to insert some text at a specific position in a line, including the ability to insert it at the end:

SELECT 
  CASE WHEN (LEN ([t0].[Product] = 8) 
    THEN [t0].[Product] + 'test' 
    ELSE STUFF ([t0].[Product], 8, 0, 'test') 
  END
FROM [OrderItem] [t0]

(CASE WHEN + LEN is required because STUFF does not allow me to insert text at the end of a line.)

The problem is that LEN eliminates trailing spaces that will ruin the computation. I know that I can use DATALENGTH, which does not exclude trailing spaces, but I cannot convert the bytes returned by DATALENGTH to the characters required by STUFF, because I don't know if the Product column is of type varchar or nvarchar.

So, how can I generate an SQL statement that depends on the exact length of the string in characters without prior information about the string data type used?

+5
source share
5 answers

Here is what I ended up using:

SELECT   
  CASE WHEN ((LEN ([t0].[Product] + '#') - 1) = 8)   
    THEN [t0].[Product] + 'test'   
    ELSE STUFF ([t0].[Product], 8, 0, 'test')   
  END  
FROM [OrderItem] [t0]

Measurements show that LEN (... + '#') is 1 trick about the same speed as LEN (...).

Thanks for the good answers!

+12

:

SELECT 
  CASE WHEN (LEN (REPLACE([t0].[Product],' ', '#') = 8) 
    THEN [t0].[Product] + 'test' 
    ELSE STUFF ([t0].[Product], 8, 0, 'test') 
  END
FROM [OrderItem] [t0]
+4

?

, , varchar nvarchar, .

create table #test
(
c varchar(50),
n nvarchar(50)
)

insert into #test values ('1,2,3,4    ',N'1,2,3,4,5      ')

SELECT
       CASE
              WHEN datalength(CAST(c AS nvarchar(MAX))) = datalength(c)
              THEN 'c is nvarchar'
              ELSE 'c is char'
       END,
       CASE
              WHEN datalength(CAST(n AS nvarchar(MAX))) = datalength(n)
              THEN 'n is nvarchar'
              ELSE 'n is char'
       END
FROM   #test
+3
source

Use DATALENGTHand SQL_VARIANT_PROPERTY:

SELECT 
  CASE 
    WHEN 8
      = DATALENGTH([t0].[Product]) 
      / CASE SQL_VARIANT_PROPERTY([t0].[Product],'BaseType') WHEN 'nvarchar' THEN 2 ELSE 1 END
    THEN [t0].[Product] + 'test' 
    ELSE STUFF ([t0].[Product], 8, 0, 'test') 
  END
FROM [OrderItem] [t0]
+1
source

If there are no leading spaces, it len(reverse(column_name))will give you the length of the column.

0
source

More articles:


All Articles