Questions tagged [patindex]
151 questions
0
votes
0 answers
How to avoid going throw (select case when)- cases that takes a lot of time
I must work with Bank-Statements which means I have to work with a big String and cut it to small Parts to be able to order every Line to which Bills it belongs.
So far so good. The problem is that PATINDEX, CHARINDEX and substring take a lot of…
memo_star
- 109
- 2
- 8
0
votes
3 answers
SQL Server 2008 Standard - Invalid length parameter passed to the LEFT or SUBSTRING function
There are many question with the same title but I think there is more to my case than meets the eye.
Here is my query:
SELECT SALH.COMPANY,
SALI.MATERIAL,
SALH.NAME1,
SALH.DEPARTMENT,
SALH.DOCTYPE,
…
Volkan Sen
- 63
- 1
- 8
0
votes
3 answers
PATINDEX, wildcards and variables
How do you get PATINDEX to to do a wildcard card match on a variable that includes a % character?
In the following I want PATINDEX to return the starting position of '%3d':
DECLARE @inputText as VARCHAR(100)
DECLARE @s as Int
DECLARE @cIn as…
Matt Hall
- 2,412
- 7
- 38
- 62
0
votes
1 answer
SQL Replace string between different charaters in comma delimited list
I am looking to remove the text between two different special characters in a string. The string is a comma-delimited list that contains a value that is immediately followed by special characters with a substring between. The characters and…
brose
- 13
- 2
0
votes
1 answer
sql: optimize query with many conditions
I have this sql query:
SELECT
[Id],
[Content]
FROM
[MyTable] with (nolock)
where
Content is not null
and (PATINDEX('%"number" : "[0-9]%', Content) > 0
or PATINDEX('%"number":"[0-9]%', Content) > 0
or…
Buda Gavril
- 21,409
- 40
- 127
- 196
0
votes
0 answers
Counting rows per special character in SQL Server
I have identify records that contain non-Alpha characters. I know how to do that part. I am using first and last name fields. The odd part of this is that I have to provide a count of rows per character. So for example, 500 rows contain $, 400…
nation161r
- 51
- 1
- 3
- 14
0
votes
2 answers
Patindex and Charindex give me more than what i'm Asking for
I have a string:
ALIS Predictions Y12 2016-17 Test Based Predictions
I'd like to return the number after the Y and have the following SQL (as an example):
SELECT SUBSTRING('ALIS Predictions Y12 2016-17 Test Based Predictions',
…
Imran
- 31
- 7
0
votes
2 answers
Search SQL Server string for values from another table
I have a table with column name that has random characters before and after the name ie:
Table A:
Name
-----------------
asd4345JONlkj345
.;lidDavidlksd$
and I have another table in same DB that has the names ie:
Table B:
Name
------
…
user3486773
- 1,174
- 3
- 25
- 50
0
votes
1 answer
SQL server 2008 patindex recursion
I want to find the latest instance of an expression, then keep looking to find there a better match and then choose the best match.
The cell I am looking at is a repeatedly apended log with notes followed by the username and timestamp.
Example cell…
jbird468
- 11
- 5
0
votes
1 answer
Invalid length parameter passed to the LEFT or SUBSTRING function 4
I realize there are similar questions here but none of them are exactly what I need. I have a SQL sproc that part of it executes a view. I have 3 databases that are identical in design and function but the data is different and I am getting this…
user3637423
0
votes
1 answer
sql date format patindex regex replace recall
Using SQL, is there an elegant way to remove the 'th' 'rd' and 'st' after a date? Here is how I'm doing it, but I'd rather do something like regex recall (example below).
Please no functions with while loops. I've already seen those solutions…
Tom McDonald
- 1,532
- 2
- 18
- 37
0
votes
1 answer
T-SQL remove leading zeroes in a string
I have string like this:
00876H873 - I want 876H873
00876 876500 - I want 876 876500
0000HUJ8 9IU877 8UJH - I want HUJ8 9IU877 8UJH
I use:
SELECT REPLACE(LTRIM(REPLACE(ColumnName, '0', ' ')),' ', '0')
This works in the first…
user3812887
- 439
- 12
- 33
0
votes
2 answers
PatIndex Pattern /Regex: How to match a dot followed by a space
Q1-PatIndex Pattern Regex: How to match a dot followed by a space?
Q2 -PatIndex Pattern Regex: How to match a dot followed by two spaces?.
I want to put it in here to get only the GOAL content
Declare @Temp Table(Data VarChar(1000))
Insert Into…
Philip Morris
- 459
- 1
- 9
- 26
0
votes
2 answers
SQL - How to return a set of numbers after a specific word
In SQL i'm trying to return the first set of numerical values after a specific word. I only want the numbers from the string after the specific word.
eg: ' hello : '
for example:
hello : 123 should return 123
hello : x123 …
WelliXL
- 3
- 2
0
votes
2 answers
Select statement does not return full result because of white space between the words
I have a select statement that's only returning a partial name and county because of white space.
The query should return: Stacey Cox but, it only returns Stacey. It should also return Wayne County but, it returns Wayne.
I have tried using RegExp…
tnlewis
- 323
- 1
- 3
- 15