ssrs expression to split string every 2 characters

Question

I am using SSRS to do the below functionality

I have a string stractual= 7700964455 

I need to split the above string for every 2 characters something like below

77 00 96 44 55

Also I have another string value strexist = "99"

I need to verify whether strexist exists in stractual to meet the condition.


77
00
96
44
55

[https://stackoverflow.com/questions/9009825/using-like-in-ssrs-expressions](https://stackoverflow.com/questions/9009825/using-like-in-ssrs-expressions) This addresses the second part of the question; chnage to parameters rather than fields — Schmocken, Aug 16 '17 at 08:04

score 0 · Answer 1 · answered Aug 16 '17 at 18:42

0

A very basic SQL Server function to do the string split would look something like this:

CREATE FUNCTION dbo.SPACEIT (@str nvarchar (255)) RETURNS nvarchar (500)
AS
BEGIN
    DECLARE @cnt int = 1;
    DECLARE @result nvarchar (500) = ''

    WHILE @cnt <= LEN(@str)
    BEGIN
      SET @result = @result + SUBSTRING(@str,@cnt,1)
      if (@cnt % 2 = 0)
          set @result = @result + ' '
      SET @cnt = @cnt + 1
    END 

    return @result
END

answered Aug 16 '17 at 18:42

gaccardo

377
2
13

I know to write in SQL but the thing here is how to do that in SSRS level I got the solution we can so that using vb script – Kapil Aug 17 '17 at 03:06

score 0 · Answer 2 · answered Aug 17 '17 at 03:18

To control the above the functionality in the SSRS level we can write vb script as below

Click on the report properties and move to Code tab and write your vb function

you can write your own logic something below

TO access the function you need to write something like below in the expressions

=Code.SubjectDetailsCheck("Value","1")

score 0 · Answer 3 · answered Sep 02 '17 at 19:48

You may use below query as a trick -

DECLARE @VAR VARCHAR(10)='702030405'
DECLARE @RNG INT=LEN(@VAR)
SELECT @RNG
SELECT TOP (@RNG) ROW_NUMBER() OVER(ORDER BY A.OBJECT_ID) R
INTO #TEMP  
FROM SYS.all_objects A
CROSS JOIN SYS.all_objects B

SELECT * , ROW_NUMBER() OVER(ORDER BY R DESC) as R1 INTO #T1
FROM #TEMP


--DECLARE @VAR VARCHAR(10)='7020304056'

Select CASE WHEN LEN(SUBSTRING(@VAR,R,len(@VAR)-R))>2 
THEN LEFT(SUBSTRING(@VAR,R,len(@VAR)-R),2)
ELSE SUBSTRING(@VAR,R,len(@VAR)-R) END SS
, ROW_NUMBER() OVER(ORDER BY R)R2
INTO #T3
FROM #T1

SELECT * FROM #T3
WHERE NOT(R2% 2=0)

DROP TABLE #TEMP
DROP TABLE #T1
DROP TABLE #T3

ssrs expression to split string every 2 characters

3 Answers3