Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Equivalent of Transform(@R)
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2008
Miscellaneous
Thread ID:
01518159
Message ID:
01518196
Views:
64
This message has been marked as a message which has helped to the initial question of the thread.
>>>>Why not doing this in FrontEnd?
>>>>I think this is better place to format strings. :-)
>>>
>>>You might be right and I am drowning on a glass of water, the reason, if you allow to call my thoughts reasonable, is that the end string has some other commas that should stay and other digits that should go without separation, for a simple example SQL will return 7201,,,1,0,1,123,456,#,,,3,# and I need 7201,,,1,0,1,1,2,3,4,5,6,#,,,3,#, so I will need to parse this (and there are more variants, only one for now but I like to keep my options open as in the future there might be another, in this case 101 indicates one type of list) in order to add only the commas needed, but then... it might be easier than what I was thinking...
>>
>>This is not very easy in T-SQL. You will need a table of numbers, operate with each value (character) separately and then finally concatenate the result back. E.g. not too complex, but definitely not using just functions.
>
>Thanks Naomi, and Boris, I forgot to thank him before

You could write a function, (UDF) in SQL Server. I called this MakeDialString

use Test;
GO
IF OBJECT_ID (N'dbo.MakeDialString', N'FN') IS NOT NULL
DROP FUNCTION dbo.MakeDialString;
GO
CREATE FUNCTION MakeDialString (@phone VARCHAR(40))
RETURNS VARCHAR(40)
with execute as caller
as
BEGIN
DECLARE @intFlag INT
DECLARE @phone2 VARCHAR(40)

SET @intFlag = 1
SET @phone2 = ''

WHILE (@intFlag lessthan 11) BEGIN
set @phone2 = @phone2 + substring(@phone,@intflag,1) + ','
set @intFlag = @intFlag + 1
END
RETURN @phone2
END
GO

Rplace lessthan with the less than symbol, it would not let me post with it, must be my settings
Then use it in your select as

use Test;
GO
select dbo.MakeDialString('5555551212') as dialphone
go

Replacing the number with your PhoneNumberField

Bill
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform