Menu
I have a column which is called studentID, but I have millions of records and somehow the application has input some arbitrary text in the column.
How do I search:
SELECT * FROM STUDENTS WHERE STUDENTID CONTAINS TEXT 
999
SQL Server CHARINDEX() Function The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0.
Method 1 - Using CHARINDEX() function This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).
'SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME = 'testing';' -will give you all table values together.
We can use the CHARINDEX() function to check whether a String contains a Substring in it. Name of this function is little confusing as name sounds something to do with character, but it basically returns the starting position of matched Substring in the main String.
Leaving database modeling issues aside. I think you can try
SELECT * FROM STUDENTS WHERE ISNUMERIC(STUDENTID) = 0 But ISNUMERIC returns 1 for any value that seems numeric including things like -1.0e5
If you want to exclude digit-only studentids, try something like
SELECT * FROM STUDENTS WHERE STUDENTID LIKE '%[^0-9]%' Just try below script:
Below code works only if studentid column datatype is varchar
SELECT * FROM STUDENTS WHERE STUDENTID like '%Searchstring%' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
