search on sql table

[PROKOPIS]

hi

am trying to implement a hard sql command (for me) and i need some help

i have a variable(name) in which the value of the name may have either at the end of value or somewhere on the value a number. this number is not fixed(ex of value: prokopis04092015, or prok04092015pis). how i have to add the certain value on the database,table(prokopis) in order to create the select command and retrive the values?
do i have to add it something like that(prokopis%, or proko%pis)
also if this can be done how can i create the select command?

thanks

 

[simian336]

Not sure that I exactly understand what your trying to do. Perhaps a better example with more data would help.

But yes, if I understand you could find it with

select Name
from [dbo].[UserInfo]
where Name like 'prokopis%'
or Name like 'proko%pis'

but in this example this would also work

select Name
from [dbo].[UserInfo]
where Name like 'proko%'

Are you trying to identify where the number starts and ends? Or what the number is?

Simi

 

[Olaf Doschke]

You could use something like 10 nested REPLACEs to remove all 10 digits from 0 to 9. and finally compare the rest with 'prokopis'

SELECT * FROM table WHERE 
REPLACE(
 REPLACE(
  REPLACE(
   REPLACE(
    REPLACE(
     REPLACE(
      REPLACE(
       REPLACE(
        REPLACE(
         REPLACE(name
         ,'0','')
        ,'1','')
       ,'2','')
      ,'3','')
     ,'4','')
    ,'5','')
   ,'6','')
  ,'7','')
 ,'8','')
,'9','') = 'prokopis'

Another way would need a stored procedure removing digits or any other pattern, you'll find such functions googling "T-SQL to remove digits from string".

Bye, Olaf.

 

[PROKOPIS]

am sorry i might not set correct my question

i have on tthe table on collumn name the value prokopis%.
the variable that i have it might be prokopis123456 or proko123pis. i want this to much it with the value on the table.

select Name
from [dbo].[UserInfo]
where Name like variable

on this command the red variable is the prokopis123456 or proko123pis. i can;t not edit this. is as the user give it to me.

 

[Olaf Doschke]

LIKE clause doesn't work this way around.

If your name column contains 'prokopis%' it's the like patter and the user input in a variable would be tested against that pattern with variable like name, not name lke variable. Eg 'prokopis123456' LIKE 'prokopis%' is TRUE, 'proko123pis' LIKE 'prokopis%' is FALSE, but both 'prokopis%' LIKE 'prokopis123456' and 'prokopis%' LIKE 'proko123pis' are FALSE. In the term A LIKE B the B is the patter containing wildcards, A is the value tested against the pattern, if A contains wildcard characters it doesn't make T-SQL turn the logic around, these wildcard characters are then taken 1:1. If the user input should work as pattern, the name LIKE userinputvariable would be right, but users would need to input % or _ wildcards where needed to find names with wildcard matches. That's the normal case, the database doesn't contain the patterns you search, it contains the names.

If it's still like you say, your name column should rather be called namepattern to make clear it's not storing names but search/like patterns. It also should be varchar, otherwise all trailing spaces would also need to be in the user input for the match to work, eg 'prokopis%' stored in a char(20) field is 'prokopis% ' and this means userinput LIKE 'prokopis% ' would only match all user input starting with prokopis AND ending in 11 spaces.

For such simple questions or operator and value order books online is your source of answers.

Your first post sounded like you want to match against a like clause not existing, a match of any string, which is 'prokopis' (or any other user input), if you remove all digits from it, like both 'proko123pis' or 'prokopis123456' are. If that is really the case my answer would do that, if you simply use the user input variable instead of 'prokopis'

Bye, Olaf.

 

[Olaf Doschke]

The usual use case is a search form where users may enter partial names and you prefix and suffix % then, so users don't need to enter the wildcard and know it. That would rather be ... WHERE name LIKE '%'+userinput+'%' and would eg fit 'prokopis' if users enter 'oko'. Anyway the syntax is value LIKE pattern.

Bye, Olaf.