April 20, 2006 at 5:24 pm
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
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.
April 20, 2006 at 6:15 pm
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.
April 21, 2006 at 2:21 am
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.
April 21, 2006 at 8:47 am
Thanks !
I have long time with out uses the substring so I was thinking that the sintaxis was like mid$() function in VB.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply