-2

REGEX is not available in SQL and Cannot have access to Visual Studio .

There is one output here shorturl.at/ovACN but its for MYSQL.

What I need is for SQL.

REGEX is not working with SQL.

When we are trying to do 

REGEXP '^[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]'

in SQL its giving non boolean expression error.

How can I get only these patterns (R123456,m123456,y729472) and not like these (R12AS56,mS23456,y7294D2)

Like operator is not working and not getting what exactly I should use to get this output (R123456,m123456,y729472)

MYSQL 

where COlumn name REGEXP '^[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]';

ORACLE:

where REGEXP (COlumn nane,'^[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]');

Output = R123456,m123456,y729472

Actual error msg with REGEXP function

Rohan
  • 27
  • 5
  • SQL SERVER MANAGEMENT STUDIO I am using .But when i am keeping regexp in my function its giving Non Boolean Expression Error.How to solve this issue – Rohan Sep 07 '19 at 09:37
  • Select * from table where Column name REGEXP '^[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]'; Error(s), warning(s): An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'. – Rohan Sep 07 '19 at 09:41
  • `LIKE 'R[0-9][0-9][0-9][0-9][0-9][0-9]'` [RTFM](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017#pattern-matching-by-using-like) – Luuk Sep 07 '19 at 09:48
  • Cannot give hard coded value like 'R%' or 'W%' .It can be anything from[a-zA-Z] and then 6 digits[0-9][0-9]... @Luuk – Rohan Sep 07 '19 at 09:51
  • @Julian I am not having access to VS for making some procedures as seeing that link which you have shared....Isnt any simple operator available for getting this pattern – Rohan Sep 07 '19 at 10:01
  • No Output using Like operator http://sqlfiddle.com/#!9/a5b38e/19 Tried with different combos .not working . Please anyone can help ..its urgent issue – Rohan Sep 07 '19 at 10:17
  • What database are you using? In your sqlfiddle you have chosen `MySQL`.... ?? – Luuk Sep 07 '19 at 10:36
  • SQL db...i am not getting how can I crack that pattern.its Mysql preselected there @Luuk. Can you or anyone help with exact function to get desired output – Rohan Sep 07 '19 at 11:17
  • SQL is tooo broad. There are different implementation, from different suppliers. Like MSSQL (or Microsoft SQL), ORACLE, MySQL, MariaDB, SQLite, PostgreSQL. So again, which database are you using? – Luuk Sep 07 '19 at 11:26
  • SQL server management studio 2008/MSSQL 2014...BOTH – Rohan Sep 07 '19 at 11:31
  • If you are using MS-SQL, than this works `SELECT name FROM cricket WHERE name LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9]'`. It will find all values starting with a letter, and followed by 6 digits. – Luuk Sep 07 '19 at 11:33
  • What abt sql server management system(SSMS 2008) – Rohan Sep 07 '19 at 11:37
  • Check [link](https://rextester.com/EILT14245). You will get your answer. – JIKEN Sep 07 '19 at 18:40
  • @JIKEN the code is really high level for me ...not getting .value/rdata is there...just want to ask if Like Operator and then that condition where name like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]'; will work for SQL ... for desired output – Rohan Sep 08 '19 at 08:04
  • Yes it will work. – JIKEN Sep 08 '19 at 09:32

1 Answers1

0

If you have this code in MySQL:

where name REGEXP '^[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]'

The equivalent in Oracle is:

where regexp_like(name, '^[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]')

And in SQL Server is:

where name like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9]%'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • about MSSQL variant: The 'a-z' part can be left off, if using the default(?) collating sequence, which is case insensitive. Also when looking for 1 letter followed by 6 digits, the last '%' should be left off. – Luuk Sep 07 '19 at 15:34
  • So for sql(ssms) like operator will work anf it will give the expected output of 1 letter and 6 digits therr after – Rohan Sep 08 '19 at 07:57