I have a column of data in my database that has a project number the numbers are formatted like this, YYYYnnnn.ee (for example 20140124.00). Since there are three distinct parts of the number, the user could search by either the YYYY or the nnnn. I have written a query that searches by the YYYY. I need to write a query for the nnnn.
How would you write a query to search for a specific string in a specific location of another string?
You can use SUBSTRING for that.
The first paramter to SUBSTRING should be the columname, the next parameter is the index of the start character and the last parameter is the number of characters.
The example below will return the nnnn part from your column. In the example below we are searching for records where the nnnn equals 1234.
SELECT *
FROM tablename
WHERE
(SUBSTRING(columnName, 5, 4) = '1234')
Please note, if your column is a number (decimal). You will need to first cast it as a varchar (string). For example:
SELECT *
FROM tablename
WHERE
(SUBSTRING(CAST(columnName AS VARCHAR(20)), 5, 4) = '1234')
