Return all words starting with a character in a column

Question

I have a VARCHAR column with data like this:

abc = :abc and this = :that

I need a query to find all of the special "words" that start with a colon in this column of data. I don't really need any other data (IDs or otherwise) and duplicates would be OK. I can remove duplicates in Excel later if need be. So if this was the only row, I'd like something like this as the output:

SpecialWords
:abc
:that

I'm thinking it'll require a CHARINDEX or something like that. But since there could be more than one special word in the column, I can't just find the first : and strip out the rest.

Any help is greatly appreciated! Thanks in advance!

Answer 1

You have to split this value based on spaces and return only fields that starts with a colon :, i provided 2 solutions to achieve this based on the result type you need (Table or Single Value)

Table-Valued Function

You can create a TV function to split this column into a table:

CREATE FUNCTION [dbo].[GETVALUES] 
    (   
    @DelimitedString    varchar(8000)
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = 1

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(' ', @DelimitedString)

        IF @Index = 0
            BEGIN

            IF ((LTRIM(RTRIM(@DelimitedString))) LIKE ':%')
                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

             IF (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1)))) LIKE ':%'
                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

And you can use it like the following:

DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'abc = :abc and this = :that and xyz = :asd'

SELECT
    *
FROM
 dbo.GETVALUES(@SQLStr)

Result:

Scalar-Valued Function

If you need to return a value (not table) so you can use this function which will return on all values separated by (line feed + carridge return CHAR(13) + CHAR(10))

CREATE FUNCTION dbo.GetValues2
(
    @DelimitedString    varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN

       DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint,
                    @Result varchar(8000)

    SET @DelSize = 1
    SET @Result = ''

    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(' ', @DelimitedString)

        IF @Index = 0
            BEGIN

            if (LTRIM(RTRIM(@DelimitedString))) LIKE ':%'
           SET @Result = @Result + char(13) + char(10) +  (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

             IF (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1)))) LIKE ':%'

                    SET @Result = @Result + char(13) + char(10) + (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END


    return @Result
END
GO

you can use it as the following

DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'abc = :abc and this = :that and xyz = :asd'

SELECT dbo.GetValues2(@SQLStr)

Result

in the table result line feed are not visible, just copy the data to an editor and it will appears as shown in the image

References

• Splitting the string in sql server

Answer 2

One way is to write a specialized SPLIT function. I would suggest getting a TSQL Split function off the internet and see if you can adapt the code to your needs.

Working from scratch, you could write a function that loops over the column value using CHARINDEX until it doesn't find any more : characters.

Answer 3

How about using a charindex?

rextester sample:

create table mytable (testcolumn varchar(20))
insert into mytable values ('this = :that'),('yes'), (':no'), ('abc = :abc')


select right(testcolumn, charindex(':', reverse(testcolumn)) - 1) from mytable
where testcolumn like '%:%'

reference:

SQL Select everything after character

Update

Addressing Sami's:

Didn't see that two words could be in one colon, how about this?

select replace(substring(testcolumn, charindex(':', testcolumn), len(testcolumn)), ':', '')

Update again

I see, the actual statement is this = :that and that = :this

Answer 4

If performance is important then you want to use an inline table valued function to split the string and extract what you need. You could use delimitedSplit8K or delimitedSplit8K_lead for this.

declare @string varchar(8000) = 'abc = :abc and this = :that';

select item 
from dbo.DelimitedSplit8K(@string, ' ')
where item like ':%';

returns:

item
------
:abc
:that

And for even better performance than what I posted above you could use ngrams8k like so:

declare @string varchar(8000) = 'abc = :abc and this = :that';

select position, item = 
  substring(@string, position,
    isnull(nullif(charindex(' ',@string,position+1),0),8000)-position)
from dbo.ngrams8k(@string, 1)
where token = ':';

This even gives you the location of the item you are searching for:

position   item
---------- -------
7          :abc
23         :that