Sql - How to Select Words with Certain Values at The End of Word

SQL - how to select words with certain values at the end of word

You have to remove the last %, so it will only select words ending with es.

select * from table where Name like '%es'

SQL to get whole words till end from the keyword

For SQL Server you can consider this below logic-

DECLARE @T VARCHAR(MAX) = 'Please check http://example.com'
SELECT RIGHT(@T,LEN(@T) - CHARINDEX('http:',@T,1)+1)

For MySQL-

SET @T = 'Please check http://example.com';  
SELECT RIGHT(@T,LENGTH(@T) - POSITION("http:" IN @T)+1)

In case of select query using table, queries will be-

-- SQL Server
SELECT RIGHT(column_name,LEN(column_name) - CHARINDEX('http:',column_name,1)+1) FROM your_table_name

-- MySQL
SELECT RIGHT(column_name,LENGTH(column_name) - POSITION("http:" IN column_name)+1) FROM your_table_name

To apply 'NA' when no link available, please use the below logic-

DECLARE @T VARCHAR(MAX) = 'Please check http://example.com'
SELECT 
CASE 
    WHEN CHARINDEX('http:',@T,1) >= 1 THEN RIGHT(@T,LEN(@T) - CHARINDEX('http:',@T,1)+1)
    ELSE 'NA'
END

Extract words before and after a specific word

As others have said, you can use a string splitting function to split out each word and then return those you require. Using the previously linked DelimitedSplit8K:

CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
go

declare @t table (t ntext);
insert into @t values('where it was done it will retrieve the...'),('at the end of the trip clare done everything to improve'),('we don''t take donut donations here'),('ending in don');

with t as (select cast(t as nvarchar(max)) as t from @t)
    ,d as (select t.t
                 ,case when patindex('%don%',s.Item) > 0 then 1 else 0 end as d
                 ,s.ItemNumber as i
                 ,lag(s.Item,1,'') over (partition by t.t order by s.ItemNumber) + ' '
                  + s.Item + ' '
                  + lead(s.Item,1,'') over (partition by t.t order by s.ItemNumber) as r
           from t
               cross apply dbo.DelimitedSplit8K(t.t, ' ') as s
          )
select t
      ,r
from d
where d = 1
order by t
        ,i;

Output:

+---------------------------------------------------------+-----------------------+
|                            t                            |           r           |
+---------------------------------------------------------+-----------------------+
| at the end of the trip clare done everything to improve | clare done everything |
| ending in don                                           | in don                |
| we don't take donut donations here                      | we don't take         |
| we don't take donut donations here                      | take donut donations  |
| we don't take donut donations here                      | donut donations here  |
| where it was done it will retrieve the...               | was done it           |
+---------------------------------------------------------+-----------------------+

And a working example:

http://rextester.com/RND43071

SQL SELECT LIKE containing only specific words

Assuming that column1 contains space separated words, and you only want to match on whole words, something like:

SELECT * FROM
  (select ' ' + REPLACE(column1,' ','  ') + ' ' as column1 from mytable) t
WHERE
   column1 like '% word1 %' AND
   column1 like '% word2 %' AND
   column1 like '% word3 %' AND
   REPLACE(REPLACE(REPLACE(column1,
      ' word1 ',''),
      ' word2 ',''),
      ' word3 ','') = ''

Note that this construction does allow the same word to appear multiple times. It's not clear from the question whether that should be allowed. (Fiddle)

---

It would be a far better design if these words were stored as separate rows in a separate table that relates back to mytable. We could then use more normal SQL to satisfy this query. Your example looks like it's some kind of tagging example. Having a table storing each tag as a separate row (with an ordinal position also recorded, if required) would turn this into a simple relational division problem.

---

A way to count how many times a word appears in a column is the expression:

(LEN(column2) - LEN(REPLACE(column2,'word',''))/LEN('word')

but this would again revert back to matching subsequences of larger words as well as the word itself, without more work.

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

If you need all words to be present, use this:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
  AND column1 LIKE '%word2%'
  AND column1 LIKE '%word3%'

If you want something faster, you need to look into full text search, and this is very specific for each database type.

Query to select strings end with certain character

Try this:

select col from table where col like '%\_1'

character _ is a jolly, like %, but it matches only a single character, so you have to escape it with \

See here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

SQL search for words in any order

this sort of search requirement sounds a good candidate for full text search.

Full text search is (or at least can be) more of a "search engine" like search as opposed to the traditional sql like searches. With full text searching, the order of the words being searched for does not matter, and depending on the RDBMs some full text searching capabilities allow for synonym lookup, as well as noise word filtering.

In (i believe) most cases, full text searching is significantly faster than a like search. Here is an article on getting started with full text search in mysql.

Example mySql full text search syntax:

select *
from items
where match(item_name) against ('+lorem +ipsum' in boolean mode)

Full text searching does have certain requirements (which are gone into detail in the links in the article). I've not personally worked with mysqls full text search, or I'd list out the steps. Should be enough to get you started though if you wanted to go in that direction.

Extract string from a text after a keyword

Here is an example using SUBSTRING():

SELECT SUBSTRING(YourField, CHARINDEX(Keyword,YourField) + LEN(Keyword), LEN(YourField))

Another example:

declare @YourField varchar(200) = 'Mary had a little lamb'
declare @Keyword varchar(200) = 'had'
select SUBSTRING(@YourField,charindex(@Keyword,@YourField) + LEN(@Keyword), LEN(@YourField) )

Result:

 a little lamb

Please note that there is a space before the 'a' in this string.

---

Related Topics