Find word in a string with a character

Question

I have tried so many times but could not find the exact query yet. The one I made works in few string but doesn't work in another(It is uncertain).

What i want is the word which has '.' in it like "abcde sfjhjk.dkjb sajb njdhf", what i want is "sfjhjk.dkjb" as result . This is just an example.

The query returns all letters in some cases while truncates few digits in other cases. You can check by providing different values.

I tried below :
This doesn't work:

DECLARE @QUERY VARCHAR(MAX)='
               renewreque0_.amount                   AS AMOUNT48_, 
               renewreque0_.charge_type              AS CHARGE3_48_, 
               renewreque0_.commission_rate          AS COMMISSION4_48_
'


SET NOCOUNT ON;

DECLARE @TABLENAME TABLE(TABLE_NAME VARCHAR(MAX),ALIAS VARCHAR(MAX))
DECLARE @COLUMNS_JOIN TABLE(COL VARCHAR(MAX),COLUMN_NAME VARCHAR(MAX),ALIAS VARCHAR(MAX))
DECLARE @NAME VARCHAR(MAX),@ALIAS VARCHAR(MAX),@J_QUERY VARCHAR(MAX),@W_QUERY VARCHAR(MAX)
DECLARE @WHERE_JOIN TABLE(COL VARCHAR(MAX),COLUMN_NAME VARCHAR(MAX),ALIAS VARCHAR(MAX))


 WHILE CHARINDEX('.',@QUERY)>1
BEGIN
SET @NAME = REVERSE( SUBSTRING(REVERSE(@QUERY),CHARINDEX('.',REVERSE(@QUERY))+1,CHARINDEX(' ',@QUERY))  )
SET @ALIAS= REVERSE(LEFT(REVERSE(@QUERY),CHARINDEX('.',REVERSE(@QUERY))))
SET @ALIAS=LEFT(@ALIAS,CHARINDEX(' ',@ALIAS))
SET @NAME=LTRIM(RTRIM(@NAME))
SET @ALIAS=LTRIM(RTRIM(@ALIAS))
INSERT INTO @COLUMNS_JOIN  SELECT @NAME+@ALIAS,@NAME,REVERSE(LEFT(REVERSE(@ALIAS),LEN(@ALIAS)-1))
SET @QUERY=REPLACE(@QUERY,@NAME+@ALIAS,'')
END

SELECT * FROM @COLUMNS_JOIN

This works:

DECLARE @QUERY VARCHAR(MAX)='
                   AND t8_.username LIKE ? 
       AND t4_.branch_id = ? 
       AND t1_.account_no = ? 
       AND t0_.remarks = ? 
       AND t0_.collect_from = ? 
'


SET NOCOUNT ON;

DECLARE @TABLENAME TABLE(TABLE_NAME VARCHAR(MAX),ALIAS VARCHAR(MAX))
DECLARE @COLUMNS_JOIN TABLE(COL VARCHAR(MAX),COLUMN_NAME VARCHAR(MAX),ALIAS VARCHAR(MAX))
DECLARE @NAME VARCHAR(MAX),@ALIAS VARCHAR(MAX),@J_QUERY VARCHAR(MAX),@W_QUERY VARCHAR(MAX)
DECLARE @WHERE_JOIN TABLE(COL VARCHAR(MAX),COLUMN_NAME VARCHAR(MAX),ALIAS VARCHAR(MAX))


 WHILE CHARINDEX('.',@QUERY)>1
BEGIN
SET @NAME = REVERSE( SUBSTRING(REVERSE(@QUERY),CHARINDEX('.',REVERSE(@QUERY))+1,CHARINDEX(' ',@QUERY))  )
SET @ALIAS= REVERSE(LEFT(REVERSE(@QUERY),CHARINDEX('.',REVERSE(@QUERY))))
SET @ALIAS=LEFT(@ALIAS,CHARINDEX(' ',@ALIAS))
SET @NAME=LTRIM(RTRIM(@NAME))
SET @ALIAS=LTRIM(RTRIM(@ALIAS))
INSERT INTO @COLUMNS_JOIN  SELECT @NAME+@ALIAS,@NAME,REVERSE(LEFT(REVERSE(@ALIAS),LEN(@ALIAS)-1))
SET @QUERY=REPLACE(@QUERY,@NAME+@ALIAS,'')
END

SELECT * FROM @COLUMNS_JOIN

Can anybody please help.

My approach would be to create a temporary table which stores indexes of all space characters in a string. Then you query for a dot and in the result you can extract what you need with a simple substring. Reversing is always tricky. — PacoDePaco, Sep 18 '17 at 06:17
I liked your idea @PacoDePaco I ll go for it and check if it works :) — Ranjana Ghimire, Sep 18 '17 at 07:04

Marc Guillot · Accepted Answer · 2017-09-18T13:37:11.470

I would first use an SplitString function (passing a blank space as delimiter), which returns as rows each word on a string, and then filter it to return just the words having a dot.

SQL Server 2016 already has one https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql and on older SQL Servers you can build your own : Splitting the string in sql server

set @SQLStr varchar(512) = 'abcde sfjhjk.dkjb sajb njdhf';

select value from string_split(@SQLStr, ' ')
where charindex('.', value) > 0

score 1 · Answer 2 · answered Sep 18 '17 at 07:10

Alright just for fun

declare @str nvarchar(100) = 'abcde sfjhjk.dkjb sajb njdhf',
        @pointIndex int 

 SET @pointIndex = (SELECT CHARINDEX('.',@str) )

SELECT  RTRIM(LTRIM(SUBSTRING(@str, @pointIndex - CHARINDEX(' ',REVERSE(LEFT(@str,@pointIndex))) +1,CHARINDEX(' ',REVERSE(LEFT(@str,@pointIndex)))) -- left side
    +SUBSTRING(@str,@pointIndex +1, CHARINDEX( ' ', SUBSTRING(@str,@pointIndex,len(@str) - @pointIndex) ) -1 )))

Needless to say i would not recommend this option because it is really hard to maintain. As Marc said your best option here is to split your string for blanks and find the ones with a '.'

Now if you dont have SQLServer 2016 here is a split function for you :

CREATE function [dbo].[Split]
(
@string nvarchar(max),
@delimiter nvarchar(20)
)
returns @table table
(
   [Value] nvarchar(max)
)
begin
declare @nextString nvarchar(max)
declare @pos int, @nextPos int

  set @nextString = ''
  set @string = @string + @delimiter

  set @pos = charindex(@delimiter, @string)
  set @nextPos = 1
  while (@pos <> 0)
  begin
      set @nextString = substring(@string, 1, @pos - 1)

      insert into @table
      (
          [Value]
      )
      values
      (
          @nextString
      )

      set @string = substring(@string, @pos + len(@delimiter), len(@string))
      set @nextPos = @pos
      set @pos = charindex(@delimiter, @string)
  end
  return
end

And use it as such :

SELECT * FROM dbo.Split(REPLACE(@str,' ','/'),'/')
WHERE charindex('.', value) > 0

Note that i replace blanks by another value.

Find word in a string with a character

2 Answers2