Geek asks and answers

Integer function to convert strings to sql

I have a number as follows: 13901210 I want the result to produce the following result: 1390/12/10

Input function: row bigint Output: string

How to create this function?

+3
source share
6 answers

I wonder why no one suggested this simple request

declare @input bigint=13901210

select REPLACE(Convert(varchar,Convert(date,CONVERT(varchar,@input))),'-','/')

SQL Fiddle

+4
source

You can use the STUFF function :

SELECT STUFF('abde', /*Insert from*/ 3, 0, 'C') AS Result;

Result
------
abCde

And your function will be:

CREATE FUNCTION ConvertBigintToString(@Input BIGINT)
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN STUFF(STUFF(CONVERT(VARCHAR(50),@Input), 5, 0, '/'), 8, 0, '/');
END;
GO

SELECT dbo.ConvertBigintToString(13901210) AS Result;
+1
source 
DECLARE @integer int = 13901210;
DECLARE @part1 int = @integer / 10000;
DECLARE @part2 int = (@integer - @integer / 10000 * 10000) / 100;
DECLARE @part3 int = @integer - @integer / 100 * 100;

DECLARE @result char(10) =
   CAST(@part1 AS char(4)) + '/'
 + CAST(@part2 AS char(2)) + '/'
 + CAST(@part3 AS char(2))

SELECT Part1 = @part1, Part2 = @part2, Part3 = @part3, Result = @result;
GO
+1

:

SELECT SUBSTRING(CAST(13901210 AS varchar(8)), 1,4) + '/' + SUBSTRING(CAST(13901210 AS varchar(8)), 5,2) + '/' + SUBSTRING(CAST(13901210 AS varchar(8)), 7,2)

/, ,

:

CREATE FUNCTION [dbo].[int_to_slashed_string] ( @value int )
RETURNS varchar(10)
AS
BEGIN
DECLARE @string varchar(8);
SET @string = CAST(@value AS varchar(8));
RETURN SUBSTRING(@string, 1,4)
    + '/' + SUBSTRING(@string, 5,2)
    + '/' + SUBSTRING(@string, 7,2);
END

, 8 . , .

0

If you do not want to adjust the value, you can do this:

DECLARE @nbr INT=13901210
SELECT 
    LEFT(@nbr,4)+'/'+
    SUBSTRING(CAST(@nbr AS VARCHAR(10)),5,2)+'/'+
    RIGHT(@nbr,2)

And then the function will look like this:

CREATE FUNCTION [dbo].[GetDateFormatForInt] (@nbr int)
RETURNS varchar(10)
AS
BEGIN
RETURN
    LEFT(@nbr,4)+'/'+
    SUBSTRING(CAST(@nbr AS VARCHAR(10)),5,2)+'/'+
    RIGHT(@nbr,2);
END

This will only work if it's a 10 digit number

0
source

This also works for me:

    CREATE PROCEDURE getdatestr
    @result nvarchar(12) output,
    @info bigint 
    AS
    BEGIN
    SET @result=(SELECT LEFT(CAST(@info AS VARCHAR(8)),4)+'/'+
    SUBSTRING(CAST(@info AS VARCHAR(8)),5,2)+'/'+
    RIGHT(CAST(@info AS VARCHAR(8)),2))
    RETURN @result
    END
    GO

Except that LEFT, RIGHT returns nvarchar and SUBSTRING returns a string :) In any case, try ... it gives the same result, and if you're ok with it, then this is easy to get.

0
source

More articles:


All Articles