Menu
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.
408
In SQL Server, you can use the T-SQL STUFF() function to insert a string into another string. This enables you to do things like insert a word at a specific position. It also allows you to replace a word at a specific position. character_expression is the original string.
To find the index of the specific character, you can use the CHARINDEX(character, column) function where character is the specific character at which you'd like to start the substring (here, @ ). The argument column is the column from which you'd like to retrieve the substring; it can also be a literal string.
SQL Server CHARINDEX() Function The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search.
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, '.')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
