Stack Overflow
Stack Overflow

Questions tagged [patindex]

151 questions
97
votes
20 answers

Select query to remove non-numeric characters

I've got dirty data in a column with variable alpha length. I just want to strip out anything that is not 0-9. I do not want to run a function or proc. I have a script that is similar that just grabs the numeric value after text, it looks like…
SQL_Noob
  • 1,161
  • 1
  • 9
  • 18
12
votes
4 answers

selecting the correct letter from a string

CREATE TABLE #tmpTbl (m VARCHAR(100)) INSERT INTO #tmpTbl VALUES (',[Undergraduate1]') ,(',[Undergraduate10]') ,(',[Undergraduate11]') ; GO select LEFT(m, PATINDEX('%[0-9]%', m)-1) as a, SUBSTRING(m, PATINDEX('%[0-9]%', m),…
Devora
  • 357
  • 4
  • 14
8
votes
2 answers

REGEXP_LIKE conversion in SQL Server T-SQL

I have come across this line in an old report that needs converting to SQL Server. REGEXP_LIKE (examCodes, learner_code) examCodes being the source and learner_code being the pattern. I know that SQL Server doesn't have REGEXP_LIKE and most places…
Zakerias
  • 356
  • 2
  • 3
  • 18
6
votes
1 answer

SQL Server : finding substring using PATINDEX function

I'm writing different queries in SQL Server. I have 2 tables, Employees and Departments. Table Employees consists of EMPLOYEE_ID, ENAME, ID_DEP - department id. Table Departments consists of ID_DEP, DNAME. The task is to show Employee.ENAME and his…
Kevin
  • 61
  • 3
6
votes
3 answers

SQL patindex equivalent in PostgreSQL

I am in need of Postgres equivalent of the SQL function patindex
Priya
  • 1,096
  • 4
  • 15
  • 32
5
votes
1 answer

PATINDEX with letter range exclude diacritics (accented characters)

I am trying to figure out how to use a patindex to find a range of letter characters, but exclude accented characters. If I do a straight search, using the default collate (insensitive) works just fine. However, when I search a range of letters,…
RPh_Coder
  • 833
  • 8
  • 15
4
votes
2 answers

SQL Server PATINDEX sometimes returns false found index

I have noticed that PATINDEX on SQL Server (I'm using 2016) is giving odd results. I suspect it has something to do with collations and character sets. I'm trying to get the index of the first space or hyphen using PATINDEX. On a database with the…
John
  • 173
  • 3
  • 13
4
votes
1 answer

Extract Number from VARCHAR

I have a [Comment] column of type VARCHAR(255) in a table that I'm trying to extract numbers from. The numbers will always be 12 digits, but aren't usually in the same place. Some of them will also have more than one 12 digit number, which is fine,…
JR00K
  • 55
  • 4
3
votes
1 answer

Sorting an alphanumeric column in SQL with mix of alphanumeric and alpha-only values

I have a requirement where I have to apply sort in an SQL query by an Alphanumeric column such that the values in it get sorted like the following: AD AH AK1 B1 B2 B3x B3y B11 C1 C6 C12x UIP instead of the default…
Musab M. Jafri
  • 246
  • 1
  • 4
  • 13
3
votes
3 answers

SQL - Get numbers from string after a pound (#) sign

I am attempting to get numbers from a string in SQL Server 2012 that are located after a pound (#) sign and before any spaces that follow. For instance, store numbers. Let's say we have the following: Big Box Store #450 Big Box Store #768 Little…
jderekc
  • 115
  • 1
  • 12
3
votes
1 answer

SQL server and patindex unspected result

I'm trying to verify phone numbers with NANP format. I'm using this code patindex('+1[2–9][0-9][0-9][2–9][0-9][0-9][0-9][0-9][0-9][0-9]', n) But not works as expected, some numbers that should be valid (like +14104536330) not match with the…
Carlos Garces
  • 829
  • 1
  • 11
  • 24
3
votes
2 answers

SQL Server - Join Table1 to Table2 ON substring(T1.Field) = T2.ID

I have two tables: MyOrders and MyDrivers. In table MyOrders, I have a column called Details (datatype TEXT - I know, but I didn't build the database...) In MyOrders.Details, there are sometimes comma-separated lists of numerical values which…
3BK
  • 1,338
  • 1
  • 8
  • 11
3
votes
3 answers

SQL Server searching for text in a column

I'm confused over what to use? Basically I need to have a search string that can search a single column for the occurrences of multiple phrases, each input phrase is separated by a space. So input from user would be like: "Phrase1 Phrase2 ...…
David
  • 31
  • 1
  • 1
  • 2
3
votes
2 answers

Escaping ] and ^ characters in a T-SQL "pattern" expression character class

I'm trying to emulate Oracle's RTRIM(expression, characters) in MsSql Server 2008 R2 with the following query: REVERSE( SUBSTRING( REVERSE(field), PATINDEX('%[^chars]%', REVERSE(field)), …
Teejay
  • 7,210
  • 10
  • 45
  • 76
2
votes
4 answers

How to replace a string value with single character

I want to mask the data except the last four characters. For example: If there is a varchar column (Name) with the value Rio De janerio, I want to mask it as xxx xx xxxerio. If it is a numeric column (acc number) with value 123 453 6987,then I…
MRR
  • 83
  • 3
  • 9
1
2 3
…
9 10