how would i like to convert accounting GL number
99999999999999999
to
999-99999-99-9999.999
in a query to MSSQL server 2005
i dont need to update the data, just have the STRING be converted on query.
Table: GLM_MASTER__ACOUNT Field: Account
thanks.
Add a comment
|
3 Answers
One more way using STUFF()
DECLARE @a varchar(64)
SET @a = '99999999999999999'
SELECT STUFF(STUFF(STUFF(STUFF(@a, 4, 0, '-'), 10, 0, '-'), 13, 0, '-'), 18, 0, '.')
1 Comment
Erick Petrucelli
STUFF is really a better approach, since you can use it along any other function without repeating it , i.e.: STUFF(REPLACE(Phone, ' ', ''), 5, 0, '-').You need to use SUBSTRING:
SELECT
SUBSTRING(account, 1, 3) + '-' +
SUBSTRING(account, 4, 5) + '-' +
SUBSTRING(account, 9, 2) + '-' +
SUBSTRING(account, 11, 4) + '.' +
SUBSTRING(account, 15, 3)
Comments
I was looking for something similar and found this useful, so, to give something back to the community I made a function to create dynamic masks
CREATE FUNCTION dbo.fn_MaskString (@Value NVARCHAR(MAX), @Mask NVARCHAR(MAX), @Char NVARCHAR(10))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX)
DECLARE @CharPos INT
DECLARE @Position INT
SET @Result = @Value
SET @CharPos = 1
WHILE @CharPos <= LEN(@Char)
BEGIN
SELECT @Position = CHARINDEX(SUBSTRING(@Char,@CharPos, 1), @Mask, 1)
WHILE @Position > 0 AND @Position <= LEN(@Result)
BEGIN
SELECT @Result = STUFF(@Result, @Position, 0, SUBSTRING(@Char,@CharPos, 1))
SELECT @Position = CHARINDEX(SUBSTRING(@Char,@CharPos, 1), @Mask, @Position+1)
END
SET @CharPos += 1
END
RETURN @Result
END
GO
SELECT dbo.fn_MaskString('99999999999999999', 'XXX-XXXXX-XX-XXXX.XXX', '-.')
-- RESULT 999-99999-99-9999.999
