I need to insert a space into result set from a query, I tried using the space() function and right but I have confused myself.

The Column result from the query is:

  • 1234567891234567891

I need this:

  • 1234 567891234567891 (With a space after the 4th number)

You can do this several ways. I'm assuming this is stored as a number and not a string, and I'm using RTRIM to prevent invalid datatype errors.

DECLARE @i BIGINT = 1234567891234567891

SELECT LEFT(@i, 4) + ' ' + RIGHT(@i, LEN(@i) -4) AS lefty_righty

SELECT SUBSTRING(RTRIM(@i), 0, 5) + ' ' + SUBSTRING(RTRIM(@i), 5, LEN(@i)) AS substrings

SELECT STUFF(RTRIM(@i), 5, 0, ' ') AS stuffy

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.