CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ryan Abarquez
Ryan Abarquez

Reputation: 307

SQL: Split Column Values into two Columns

I have a table tbl1

How can I split the column values by 10 (number of characters)

FROM:

Column1            
Butuan City Philippines
Zamboanga City Philippines
Manila City Philippines

TO:

Column1           Column2
Butuan Cit        y Philippines
Zamboanga         City Philippines
Manila Cit        y Philippines

Upvotes: 0

Views: 2410

Answers (4)

Geeky Ninja
Geeky Ninja

Reputation: 6052

You can use the in-build SUBSTRING() function of SQL to achieve your expected result.

For E.g.

IF (LEN(Column1) > 10)
BEGIN
    SELECT SUBSTRING(Column1, 1, 10) AS Column1,SUBSTRING(Column1, 11, LEN(Column1) - 10) AS Column2
    FROM <TABLE>
END

Upvotes: 0

japzdivino
japzdivino

Reputation: 1746

You can use the LEFT, LEN, and SUBSTRING

SELECT LEFT(Column1,10) AS 'Column1'

SELECT SUBSTRING(Column1, LEN(LEFT(Column1,10)) + 1, LEN(Column1)) AS 'Column2'

Merge the two queries:

SELECT LEFT(Column1,10) AS 'Column1', 
       SUBSTRING(Column1, LEN(LEFT(Column1,10)) + 1, LEN(Column1)) AS 'Column2'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You can use the SUBSTRING() function:

SELECT SUBSTRING(Column1, 1, 10) AS Column1,
    SUBSTRING(Column1, 11, LEN(Column1) - 10) AS Column2
FROM yourtable

Note that we do not have to worry about using indices in substring which are greater than the length of Column1, q.v. the documentation which states:

If start [second parameter] is greater than the number of characters in the value expression, a zero-length expression is returned.

and

If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

Upvotes: 4

Pரதீப்
Pரதீப்

Reputation: 93694

Use LEFT and SUBSTRING string functions. Try this

DECLARE @str VARCHAR(100) = 'Zamboanga City Philippines'

SELECT LEFT (( @str ), 10),
       Substring (( @str ), 10 + 1, Len(@str))

Upvotes: 0

Related Questions