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
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.
LEFT() or any helper functions other than SUBSTRING().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))
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'
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
