I have my table like this
4-Documento d’identità-3-Attestato di Rischio-2-Carta di Circolazione
10-Contrassegno
12-Documenti di annullo polizza-10-Contrassegno
10-Contrassegno
12-Documenti di annullo polizza-10-Contrassegno
I want to split every row to be like this
4-3-2
10
12-10
10
12-10
try this
DECLARE @table AS TABLE
(
ID INT IDENTITY(1, 1) ,
SomeText VARCHAR(500)
)
INSERT INTO @table
( SomeText )
VALUES ( '4-Documento d’identità-3-Attestato di Rischio-2-Carta di Circolazione' ),
( '10-Contrassegno' ),
( '12-Documenti di annullo polizza-10-Contrassegno' ),
( '10-Contrassegno' ),
( '12-Documenti di annullo polizza-10-Contrassegno' );
WITH cte
AS ( SELECT n = 1
UNION ALL
SELECT n + 1
FROM cte
WHERE n <= 100
),
SplitToChr
AS ( SELECT T.ID ,
SUBSTRING(T.SomeText, cte.n, 1) AS Chr
FROM @table AS T
JOIN cte ON DATALENGTH(T.SomeText) >= cte.n
AND SUBSTRING(T.SomeText, cte.n, 1) LIKE '[0-9-]'
)
SELECT T.ID ,
REVERSE(SUBSTRING(REVERSE(T.FInal),2,LEN(T.FInal))) AS Final
FROM ( SELECT DISTINCT
o.ID ,
REPLACE(( SELECT '' + chr
FROM SplitToChr AS i
WHERE i.id = o.id
FOR
XML PATH('')
), '--', '-') AS FInal
FROM SplitToChr AS o
) AS T
SAMPLE TABLE
CREATE TABLE #TEMP(STRINGCOLUMN NVARCHAR(MAX))
INSERT INTO #TEMP
SELECT '4-Documento d’identità-3-Attestato di Rischio-2-Carta di Circolazione'
UNION ALL
SELECT '10-Contrassegno'
UNION ALL
SELECT '12-Documenti di annullo polizza-10-Contrassegno'
UNION ALL
SELECT '10-Contrassegno'
UNION ALL
SELECT '12-Documenti di annullo polizza-10-Contrassegno'
You can use a function in case you need the query to be simple
Function
CREATE FUNCTION [dbo].[ConvertToNumber]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
SET @strAlphaNumeric = LEFT(@strAlphaNumeric,
LEN(@strAlphaNumeric) - CHARINDEX('-', REVERSE(@strAlphaNumeric)) + 0)
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]-%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]-%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
And your final query
SELECT DBO.ConvertToNumber(STRINGCOLUMN)STRINGCOLUMN
FROM #TEMP
RESULT
first use this split func
CREATE FUNCTION [dbo].[fnSplitString] ( @string NVARCHAR(MAX)
, @delimiter CHAR(1) )
RETURNS @output TABLE ( splitdata NVARCHAR(MAX) )
BEGIN
DECLARE @start INT
, @end INT
SELECT @start = 1
, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1
BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output ( splitdata )
VALUES ( SUBSTRING(@string, @start, @end - @start) )
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
and then use this code
CREATE TABLE #t10 ( id int identity(1,1)
, num int )
INSERT INTO #t10
select *
from dbo.fnSplitString ('4-Documento d’identità-3-Attestato di Rischio-2-Carta di Circolazione','-')
WHERE ISNUMERIC(splitdata)=1
DECLARE @x int = 1
DECLARE @y varchar(10)=''
DECLARE @q varchar(10)
WHILE @x<=(SELECT count(*)
from #t10)
BEGIN
SET @q=(SELECT num
FROM #t10
WHERE id = @x )
set @y=@y+@q+'-'
SET @x=@x+1
END
SELECT left(ltrim(@y),len(@y)-1)
--SELECT * FROM #t10
--drop table #t10
It's a bit messy, but it gets the job done :-)
CREATE TABLE dbo.Documents (
Details VARCHAR(1000)
);
GO
INSERT INTO dbo.Documents
VALUES ('4-Documento d’identità-3-Attestato di Rischio-2-Carta di Circolazione'),
('10-Contrassegno'),
('12-Documenti di annullo polizza-10-Contrassegno'),
('10-Contrassegno'),
('12-Documenti di annullo polizza-10-Contrassegno');
SELECT CASE WHEN (FirstLine + SecondLine + ThirdLine) LIKE '%-'
THEN LEFT(FirstLine + SecondLine + ThirdLine, LEN(FirstLine + SecondLine + ThirdLine) -1)
ELSE (FirstLine + SecondLine + ThirdLine) END AS CleanData
FROM (
SELECT
LEFT(Details, CHARINDEX('-', Details)) AS FirstLine,
REPLACE(CASE
WHEN REPLACE( REPLACE( LEFT(Details,CHARINDEX('-', Details, CHARINDEX('-', Details )+1)+2) , LEFT(Details, CHARINDEX('-', Details, CHARINDEX('-', Details)+1)), ''), LEFT(Details, CHARINDEX('-', Details)), '') LIKE '%[0-9]' THEN REPLACE( REPLACE( LEFT(Details,CHARINDEX('-', Details, CHARINDEX('-', Details )+1)+2) , LEFT(Details, CHARINDEX('-', Details, CHARINDEX('-', Details)+1)), ''), LEFT(Details, CHARINDEX('-', Details)), '') + '-'
ELSE REPLACE( LEFT(Details,CHARINDEX('-', Details, CHARINDEX('-', Details )+1)+2) , LEFT(Details, CHARINDEX('-', Details, CHARINDEX('-', Details)+1)), '')
END , LEFT(Details, CHARINDEX('-', Details)), '') AS SecondLine,
CASE WHEN REVERSE(LEFT(REVERSE(Details), CHARINDEX('-', REVERSE(Details))+2)) LIKE '-%'
THEN LEFT(REVERSE(LEFT(REVERSE(Details), CHARINDEX('-', REVERSE(Details))+2)), 3) ELSE '' END AS ThirdLine
FROM dbo.Documents
) AS A;
Another way of doing it in a much simpler way. Create a function to remove the alphabets from the string
Create FUNCTION dbo.RemoveAlphabets (@string VARCHAR(256))
returns VARCHAR(256)
BEGIN
IF @string IS NULL
RETURN NULL
DECLARE @Result VARCHAR(256)='',@len INT = Len(@string ),@cnt INT=1
WHILE @cnt <= @len
BEGIN
DECLARE @parse INT
SET @parse = Ascii(Substring(@string , @cnt, 1))
IF @parse BETWEEN 48 AND 57 or @parse =45
SET @Result = @Result + Char(@parse)
SET @cnt = @cnt + 1
END
select @result= replace(@Result,'--','-')
RETURN left(@result,case when right(@Result,1)='-' then len(@Result)-1 else len(@result) end)
END
If numbers inside the string is always surrounded by '-' then replace the return statement with this.
RETURN left(@result,len(@Result)-1)
execute the function
select dbo.RemoveAlphabets
('4-Documento d’identità-3-Attestato di Rischio-2-Carta di Circolazione')
Result : 4-3-2
