Strange think with a string chain

Question

Strange think with a string chain

Teresita Castro

SSCommitted

Points: 1568
More actions
April 20, 2006 at 5:24 pm

#93957

Hi I have a sp that will read a line an divide the information that it have.
The problem is that when I try to obtain substring(@strLinea,56,85) it returns me strange results.
declare @strLinea varchar(186)
declare @strCabecera varchar(52)
set @strLinea='L0002         2                   a                   m                             n00000000000000000033500.21                                                 3            3000000000003'
set @strCabecera= 'H00110004190001011900010119000101Q000006000003001.26'
declare @intNoCliente int
declare @strNoEmple varchar(10)
declare @strApePaterno varchar(20)
declare @strApeMaterno varchar(20)
declare @strNomEmple varchar(30)
declare @strNomEmple2 varchar(30)
--PARAMETROS DE LAS LINEAS

set @strNoEmple = convert(int,substring(@strLinea,6,15))

set @strApePaterno = substring(@strLinea,16,35)
set @strApeMaterno = substring(@strLinea,36,55)

set @strNomEmple = substring(@strLinea,56,85)
set @strNomEmple2 = dbo.F_TRIM(substring(@strLinea,56,85))

select @strNomEmple nomem
select @strNomEmple2 nomem2
select substring(@strLinea,56,85) linea
Results:
nomem='                             n'
nomem2='n00000000000000000033500.21'
linea='                             n00000000000000000033500.21                             '

This is the function code
--Hace la funcion del TRIM, quita los espacios a la derecha y a la izquierda
--de la cadena que mandemos
--@Cadena
CREATE FUNCTION F_TRIM
(
    @Cadena varchar(200) --Cadena Original

)
RETURNS VARCHAR(200)
AS
BEGIN
    RETURN (RTRIM(LTRIM(@Cadena)))
END

I really don't now what I'm doing wrong.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mrivera Ten Centuries Points: 1050 More actions · Answer 1

I think the confusion happens when you assign the value of the substring to @strNomEmple which can only be 30 characters long. If you change the declaration to declare @strNomEmple varchar(85) you will have consistent results.

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Your problem is the misuse of the SUBSTRING function

SUBSTRING ( expression , start , length )

try this

set @strNoEmple = convert(int,substring(@strLinea,6,10))

set @strApePaterno = substring(@strLinea,16,20)

set @strApeMaterno = substring(@strLinea,36,20)

set @strNomEmple = substring(@strLinea,56,30)

set @strNomEmple2 = dbo.F_TRIM(substring(@strLinea,56,30))

select @strNomEmple nomem

select @strNomEmple2 nomem2

select substring(@strLinea,56,30) linea

Far away is close at hand in the images of elsewhere.
Anon.

Teresita Castro SSCommitted Points: 1568 More actions · Answer 3

Thanks !

I have long time with out uses the substring so I was thinking that the sintaxis was like mid$() function in VB.