CODEWITHSUNDEEP
sql-server
Rahul Jain
Rahul Jain

Reputation: 622

Query for Finding Hyphen in Sql Server

I have the records in the database like this which are as follows. I want to search in the Account Column which is my last column in my AccountDetails Table. I want to searching based on conditions like

1- want to findt hose records which contain a hyphen after 1st number (which can be anything)
12- want to find those records which contain a hyphen after 2nd number (which can be anything)
123-want to find those records which contain a hyphen after 3rd number (which can be anything)

AccountDetail Table

230263  jba 100-1807
230263  jba 100-1808
230263  jba 100-1809
235572  jba 99-1818
235572  jba 99-1819
235572  jba 99-1820
235572  jba 9-1818
235572  jba 9-1819
235572  jba 9-1820

I have done like this 

Select * From WF_Account Where CompanyId = 'jba' and Account Like '%-%'

Upvotes: 1

Views: 20543

Answers (4)

stevieg
stevieg

Reputation: 662

Use LIKE's [] character set option. See also the ESCAPE clause if you need to put a hyphen into a character set.

select * from WF_Account
-- Hypen after one digit
where Account like '[0-9]-%'
-- Hypen after two digits
or Account  like '[0-9][0-9]-%'
-- Hypen after three digits
or Account  like '[0-9][0-9][0-9]-%'

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36681

How about this using Regular expression.

1- want to findt hose records which contain a hyphen after 1st number (which can be anything)

Select * From WF_Account Where CompanyId = 'jba' and Account Like '_-%';

12- want to find those records which contain a hyphen after 2nd number (which can be anything)

Select * From WF_Account Where CompanyId = 'jba' and Account Like '__-%';

123-want to find those records which contain a hyphen after 3rd number (which can be anything) 

Select * From WF_Account Where CompanyId = 'jba' and Account Like '___-%';

Upvotes: 1

Chotip
Chotip

Reputation: 54

You can try this query:

1- want to find those records which contain a hyphen after 1st number (which can be anything)

SELECT *
FROM WF_Account
WHERE CompanyId = 'jba'
AND CHARINDEX('-', Account) = 2

12- want to find those records which contain a hyphen after 2nd number (which can be anything)

SELECT *
FROM WF_Account
WHERE CompanyId = 'jba'
AND CHARINDEX('-', Account) = 3

123-want to find those records which contain a hyphen after 3rd number (which can be anything)

SELECT *
FROM WF_Account
WHERE CompanyId = 'jba'
AND CHARINDEX('-', Account) = 4

Upvotes: 2

Lasse Edsvik
Lasse Edsvik

Reputation: 9298

Not sure which version of sql you are running.

but in SQL Server you can use CHARINDEX or in sql 2012 FINDSTRING

e.g 

WHERE CHARINDEX('-', Col1) > 0

EDIT:

You can also use PATINDEX which returns the position of string for a pattern.

so maybe:

WHERE PATINDEX('%-', Col1) <= 3

Upvotes: 4

Related Questions