January 28, 2015 at 12:28 pm
Hi
I need help in a query...How to parse a string to equal length substrings in SQL
I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..
CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp
after parsing I have to use these values in a where clause like this
IN('000001','0T0001','0L0001','000T01','0C0001')
Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow..
So the parsing should be dynamic.
Any help please
Thanks [/font]
January 28, 2015 at 1:03 pm
Here is how I would solve this problem, however there may be an easier solution:
CREATE FUNCTION [dbo].[SplitByXChars]
(
@string nvarchar(max),
@segmentLength int
) RETURNS @t TABLE
(
val nvarchar(max)
)
AS
BEGIN
declare @i int = 1
declare @stringlength int = len(@string)
while (@stringlength - (@i-1) > 0)
begin
insert into @t(val)
select substring(@string, @i, @segmentLength)
set @i = @i + @segmentLength
end
RETURN
END
Example use:
select val
from [SplitByXChars]('0000010T00010L0001000T010C0001',6)
or
select 'test'
where '000001' in (
select val
from [SplitByXChars]('0000010T00010L0001000T010C0001',6)
)
Note: the "6" in the above examples indicates to split every 6 characters.
January 28, 2015 at 1:23 pm
No need for a loop and no need to cross post as it only fragments the solutions.
Further answers in this thread: http://www.sqlservercentral.com/Forums/Topic1655566-392-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply