CAST/Convert

[PRAR]

I need Query syntax to cast/convert values as follws.

Val.: 00005000010260002180 - Result must be: 5.1.2600.2180
Val.: 00005000000213400001 - Reslut must be : 5.0.2134.1

Dots must also be contained in result

[mikr0s]

DECLARE @mytbl TABLE (the_text CHAR(20))
INSERT INTO @mytbl(the_text)
VALUES('00005000010260002180')

INSERT INTO @mytbl(the_text)
VALUES('00005000000213400001')

--the following query works when all values of the_text have 20 characters (fixed length)


SELECT CAST((SUBSTRING(the_text, 1, 5))+0 AS VARCHAR(5))+'.'+
CAST(SUBSTRING(the_text, 6, 5)+0 AS VARCHAR(5))+'.'+
CAST(SUBSTRING(the_text, 11, 5)+0 AS VARCHAR(5))+'.'+
CAST(SUBSTRING(the_text, 16, 5)+0 AS VARCHAR(5))
FROM @mytbl


-- HTH --

[PRAR]

Tks a lot. Works great