CODEWITHSUNDEEP
sql-servert-sql
s.brody
s.brody

Reputation: 185

Return column substring in query result set?

Let's say I have a string column named Module of type nvarchar(max), which holds information that is structured as multiple fields separated by underscore. But it can also have a random format, not separated by underscore.

"Field1_Field2_Field3_Field4_Field5"
"Field1_Field2_Field3_Field4_Field5_Field6"
"Field1_Field2_Field3"
"RandomString"

I want to construct a T-SQL query that returns information from that column, among other things. If an instance of that column has a random format, then return in entirely in its original format. If it's in the first format described above, then return only the first 4 fields that are separated by underscore. Basically a substring of the original string.

Result set:

"Field1_Field2_Field3_Field4"
"Field1_Field2_Field3_Field4"
"Field1_Field2_Field3"
"RandomString"

How would I go about achieving this? I was thinking of a regular expression that can be implemented in a CLR procedure, but I haven't worked with them before.

Upvotes: 0

Views: 610

Answers (3)

Tirthak Shah
Tirthak Shah

Reputation: 515

It may help.

  Declare @Input as varchar(50) = 'Field1_Field2_Field3_Field4_Field5'
  DEClare @Character as CHAR(1) = '_'
  DECLARE @StartIndex INT, @EndIndex INT


DECLARE @Output TABLE (ID int IDENTITY(1,1),
  Item NVARCHAR(1000)
)

SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

Declare @OutputString as NVARCHAR(1000) = ''

Select @OutputString = @OutputString +  Item + '_'
from @Output 
where ID < 5

Select LEFT(@OutputString, LEN(@OutputString) - 1) FinalOutput

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

How about this? This should work with the scenarios you provided in the sample data.

SELECT CASE 
        WHEN charindex(removeelement, module) - 1 > 0
            THEN substring(module, 0, charindex(RemoveElement, module) - 1)
        ELSE module
        END MagicResult
FROM (
    SELECT SpecialChar
        ,module
        ,reverse(substring(reverse(module), 0, charindex(specialchar, reverse(module), 1))) 'RemoveElement'
    FROM (
        SELECT substring(module, patindex('%[^a-zA-Z0-9]%', module), 1) 'SpecialChar'
            ,module
        FROM test
        ) rs
    ) rs

Also, here is a SQLFiddle with how this code works on the sample data you provided.

Upvotes: 1

Eugene Myasyshchev
Eugene Myasyshchev

Reputation: 4635

It can be done with the CLR procedure but I would rather do it on the front-end side. Fetch the data and then process with string.Split('_') for example similar to this:

var field = "Field1_Field2_Field3_Field4_Field5";
var parts = field.Split('_');
if (parts.Length >= 4)
{
    parts = parts.Take(4).ToArray();
    Console.WriteLine(string.Join("_", parts));
}
else
{
    Console.WriteLine(field);
}

Upvotes: 0

Related Questions