Questions tagged [patindex]
151 questions
1
vote
1 answer
I need to select multiple strings that match a patindex from a single string in SQL Server
I need to select multiple strings from a single string in that match a patindex. I am selecting the first one using the following code:
CASE WHEN comments like '%[0-9][A-Z][0-9].[0-9]%'
THEN…
Todd Ensworth
- 11
- 1
- 2
1
vote
4 answers
How do I extract only the data before a pattern string
How do I extract only the string after CX_EduDegree=??????? and replace the %20 with spaces. The data is separated by spaces, and is never in the same position.
I have tried to use the patindex with substring and replace. But I have had no…
Lee
- 11
- 2
1
vote
1 answer
Find out 0 position from string in hive
I want to know the position of the data which doesn't start with 0.
Like below example, in first row first digits are 00 and it returns the 3 position as non zero values start from 3 position
member_id values
008507743 3 …
Gurpreet Singh
- 197
- 3
- 6
- 14
1
vote
0 answers
How to match all characters except right crotchet (close square bracket) with SQL's PatIndex?
In the below code example, all results should return 7.
Those with aliases beginning X however, do not.
select
--where matches
patindex('%-%' ,'111111-11') dash --not a special character, so works without…
JohnLBevan
- 22,735
- 13
- 96
- 178
1
vote
1 answer
Sql find the last non-numeric character in a string
How to find last non-numeric character in a string such as "10jnklgm51". In order to find 'm' in the example what is the best simple way?
web surfer
- 13
- 1
- 3
1
vote
0 answers
T-Sql 2008 PatIndex error with 'AA' string
I'm running this code on SQL2008 to remove non-aphanumerical characters from strings :
snip from my function stralpha:
...
Set @KeepValues = '%[^a-z0-9]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues,…
Stig Meyer Jensen
- 76
- 7
1
vote
1 answer
SQL- Need numbers from a column for string
I have a table like this.
MES_id MES_for_col
4717 4717 = ( 4711 + 4712 + 4713)/ 3
4729 4729 = ( 4723 + 4724 + 4725 + 4726)/4
4788 4788 = ( 4780 + 4781 + 4782 + 4783 + 4784 + 4785 )/6
4795 4795 = ( 4793 + 4794 ) / 2…
omkar patade
- 1,442
- 9
- 34
- 66
1
vote
2 answers
T-SQL PATINDEX -1
this is my first post here and i'm also a newby in development.
In any case.. my problem is:
sql statement:
SELECT left(clienti.SedeLegaleIndirizzo, patindex('%[0-9]%',clienti.SedeLegaleIndirizzo))
AS indirizzo from…
Alessio Bacin
- 71
- 1
- 1
- 10
1
vote
2 answers
Transact-SQL collate doesn't work
I'm trying to use PATINDEX function like this:
select PATINDEX('%[A-Z].%', 'he.llo MA. asd ' collate Cyrillic_General_CS_AS)
I expect it returns 9 but it returns 2.
Can someone enlight me what's wrong? I also tried to supply collate in first…
heximal
- 10,327
- 5
- 46
- 69
1
vote
2 answers
Parsing through a column to flip names using patindex
So I have a database of customers. I run SELECT * FROM MyTable it gives me back several columns, one of which is the name. Looks like this:
"Doe, John"
"Jones, Bill"
"Smith, Mike"
"Johnson, Bob"
"Harry Smith"
"Black, Linda"
"White, Laura"
etc. Some…
Dylan James Creighton
- 27
- 1
- 5
1
vote
1 answer
C# equivalent of PATINDEX function in SQL Server 2008
I have the following SQL Server code which I would like to do in C# code.
The logic is - if the pattern '%SELECT %FROM% is found in a string called 'x', then I need to return. The '%' stands for 0 or more characters.
The part I am not getting is…
Sunil
- 20,653
- 28
- 112
- 197
1
vote
2 answers
Using PatIndex and CharIndex like the InStr function
I tried using Instr for this, but didn't work, so I found the supposed to be equivalent using PatIndex and CharIndex, however, I'm having an issue here when I run this:
SELECT PATINDEX(`http://www.stackoverflow.com`,'%.com%')
This returns 0
SELECT…
Control Freak
- 12,965
- 30
- 94
- 145
1
vote
1 answer
convert varchar to int in select statement using patindex
My query is for convert varchar into string,
select top(5)'Insert into jobs(minexperience,maxexperience)values('+
cast(substring(Experience as varchar(50)),0,patindex('%to%',Experience))*365*24*60*60,
cast(substring(Experience as …
PoliDev
- 1,408
- 9
- 24
- 45
1
vote
1 answer
Date calculation using Getdate() and patindex
Hopefully this is a simple one
I want to use getdate() to tell me todays date (Done) ,
then i want to take the numbers portion from a column (Don)
Then I want to add 1 and 2 and generate a column damed "Date Expires" . for example if the…
user1531300
- 15
- 2
0
votes
0 answers
Remove non-numeric characters from string and Keep zeros in start of the string
Keep zeros at the start of the string and remove all non-numeric.
I need to remove non-numeric characters from a string where non-numeric characters can be at any place of the string so would need to use a function.
I found this method which works…
CoolBreeze-Dev
- 1
- 1