MS SQL Like expression match character 1 or 0 times

Status: Closed
Prize: $50
Entries Received: 3
Winner: yuriyvsl

Winner

Contest Brief

Dear All ,
Hi,

On my database,I want to make a query like the following line :

Select *
from tableA
where
columnX
like '%A(Any character only ONE time, or noting)B(Any character only ONE time, or noting)C(Any character only ONE time, or noting)D(Any character only ONE time, or noting)E%'

A sample result would be like the followings

ABCDE
A BCDE
A B C.DE
A.BC+D E
A+B CD.E

and hundreds of variations

Please notice that, between each letter(ABCDE), there is always
1- Either one character ,including white space , comma, dot, numeric, letter ( BUT ONLY one character not 2 or 3 or 4 characters)
2- Or nothing ( not even space)

PLEASE NOTE : i am not looking for like %A%B%C%D%E' , because it will give results like A+-BCDE or A B.C-*/E too

Thanks in advance.

Recommended Skills

Winner

Post a Contest Like This

Employer Feedback

“beyond excellent, very skillful and deep knowledge of MS SQL. Definitly suggest him.”

vedatg80, Turkey.

Public Clarification Board

riskypathak
- 11 years ago
Is it ok to use COM objects(regular expression) for achieving this.
- 11 years ago
View 3 more messages
1. Contest Holder
  - 11 years ago
  please feedback if you plan to do, because the other solution is accepted ok, but is slow for 1000000 lines
  - 11 years ago
2. Contest Holder
  - 11 years ago
  if you could come with something faster it is also a second solution for me
  - 11 years ago
Contest Holder
- 11 years ago
CREATE TABLE #Test ( ColumnX nvarchar(255) )

INSERT INTO #Test (ColumnX)
VALUES ('8200118115') ,('820011 8115') ,('8+200118115') ,('8200-118 115') ,('8200 118 115') ,('820011811x5') ,('8200;118115') ,('8200 118 11 5') ,('820 011811 5'),
('82-00118c1 1s5') ,('8+200118115') ,('8.200.118 11x5') ,('820011x811x5') ,('82 001s18.115') ,('820s0 1 1 8 1 15') ,('820 011 81 15') ,('8 20011811 5')
,('82 00 118 115') ,('82 00118 115') ,('82 00118 115') ,('820 0118 115') ,('8 2 00188115') ,('820011 8 1 15') ,('820 0 11 81 15') ,('8 2001 181 1 5')

select * from #test
- 11 years ago
View 1 more message
1. mpalgeo
  - 11 years ago
  congrats yurivsl
  - 11 years ago
2. yuriyvsl
  - 11 years ago
  thanks :-)
  - 11 years ago
mpalgeo
- 11 years ago
how can i send you my code for review ?
- 11 years ago
View 3 more messages
1. yuriyvsl
  - 11 years ago
  invitation did not help, private message only
  - 11 years ago
2. Contest Holder
  - 11 years ago
  please add me from sk.y.pe vedatg80
  - 11 years ago
ugurlu2001
- 11 years ago
is Colum1 is 1 char length or a char set? Question is not clear. Do you post here column1 value record set by create table syntax.
- 11 years ago
1. Contest Holder
  - 11 years ago
  CREATE TABLE #Test ( ColumnX nvarchar(255) )
  
  INSERT INTO #Test (ColumnX)
  VALUES ('8200118115') ,('820011 8115') ,('8+200118115') ,('8200-118 115') ,('8200 118 115') ,('820011811x5') ,('8200;118115') ,('8200 118 11 5') ,('820 011811 5'),
  ('82-00118c1 1s5') ,('8+200118115') ,('8.200.118 11x5') ,('820011x811x5') ,('82 001s18.115') ,('820s0 1 1 8 1 15') ,('820 011 81 15') ,('8 20011811 5')
  ,('82 00 118 115') ,('82 00118 115') ,('82 00118 115') ,('820 0118 115') ,('8 2 00188115') ,('820011 8 1 15') ,('820 0 11 81 15') ,('8 2001 181 1 5')
  
  select * from #test
  - 11 years ago
truongnguyen86
- 11 years ago
what kind of general solution you want, can you explain more?
- 11 years ago
1. Contest Holder
  - 11 years ago
  i need a solution that would fit to any string , for example if i search for HpLaserJet it should also find solutions like H pLaserJet or HpLaserJe.t or HpLas erJ+et or Hp La se rJet or HpLa-serJet
  - 11 years ago
2. Contest Holder
  - 11 years ago
  please notice the common pattern here is that , between any two characteres, there can be EITHER 1 single character( anything) or no character
  - 11 years ago
Contest Holder
- 11 years ago
DEAR ALL, Please note, i am looking for a generic solution not the solution for 3 characters, please consider results like A.B C+DE-F.
- 11 years ago
1. yuriyvsl
  - 11 years ago
  it is possible with create a sql function. Is it a appropriate solution?
  - 11 years ago
2. Contest Holder
  - 11 years ago
  yes you can write a Sql function no problem
  - 11 years ago
Contest Holder
- 11 years ago
There is only 1 column, and it is nvarchar(255), the data can be of different sizes.
- 11 years ago
truongnguyen86
- 11 years ago
This one you want to implement in MSSQL right?
- 11 years ago
1. Contest Holder
  - 11 years ago
  yes exactly, do you have a solution
  - 11 years ago
2. Contest Holder
  - 11 years ago
  i am looking something that will fit into _ wildcard, that will return results of space, null and 1 character (anything )
  - 11 years ago