To split numbers using sql server

Question

I have a select query I want to split the number into 3 values. Please help me to do this.

SELECT Code FROM Source

Code
----------------
21859633525935

I want to split the Code into 3 values and display in 3 columns using SELECT query, i.e.:

First value      Second value     Third value
--------------------------------------------------- 
21               85963            3525935

Answer 1

If the input is a character type (char,varchar) then the various string functions will do this:

SELECT 
    First  = LEFT(code,2), 
    Second = SUBSTRING(code, 3, 5), 
    Third  = SUBSTRING(code, 8, 14) -- or RIGHT(code, 7)
FROM Source

If it's a number type (eg bigint) you have to convert it to a char type first:

SELECT 
    First  = LEFT(CAST(code AS varchar(14)),2), 
    Second = SUBSTRING(CAST(code AS varchar(14)), 3, 5), 
    Third  = SUBSTRING(CAST(code AS varchar(14)), 8, 14)
FROM Source

Or (if it's a number) you could use arithmetic:

SELECT 
    CAST(code / 1000000000000 AS int),
    CAST((code / 1000000000000-CAST(code / 1000000000000 AS int))* 1000000 AS int),
    code % 10000000
FROM Source

Answer 2

FIRST CHECK DATA TYPE OF CODE AND THEN SPLIT IT

 IF (SELECT data_type 
    FROM Information_Schema.Columns 
    WHERE Table_Name= 'SOURCE'
    AND Column_Name = 'CODE' ) LIKE '%CHAR' --CHECK DATA TYPE OF CODE
BEGIN 
SELECT    substring([Code] ,1,2)   [First value],
          substring([Code] ,3,5)   [Second value],
          substring([Code] ,8,7)   [Third value]
FROM Source
END
ELSE
BEGIN
SELECT    substring(CONVERT(VARCHAR(50),[Code]) ,1,2)   [First value],
          substring(CONVERT(VARCHAR(50),[Code]) ,3,5)   [Second value],
          substring(CONVERT(VARCHAR(50),[Code]) ,8,7)   [Third value]
FROM Source
END