Questions tagged [patindex]
151 questions
1
vote
1 answer
Regex to remove " ' and space from column
I need to select column from a table removing " ' and space and that is to be exported in a csv file. for example if the column contain "155/229-230 Moo 10 , Nuanchan Road" I shall have to remove the leading and ending quotes to 155/229-230 Moo 10 ,…
Debanjan Hazra
- 11
- 1
1
vote
0 answers
How to find out the last rebooting time of the current server in SQL Server?
Since I already know how to get the CPU speed of the current server in SQL Server and I can find out the last time SQL Server service started and the total RAM memory on the current server
Now I would like to know when it was the last time the…
Marcello Miorelli
- 3,368
- 4
- 44
- 67
1
vote
2 answers
how to filter out the year using mask characters or any other trick in the string below?
I have this function to clear off the unwanted characters:
USE MASTER
GO
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION, SCHEMABINDING
AS
BEGIN
…
Marcello Miorelli
- 3,368
- 4
- 44
- 67
1
vote
2 answers
String value shifting in SQL Server
Not sure how to clearly describe what I'd like to achieve, hope my question below does make some sense.
Let's suppose I have the string ABCDE. I would like to iterate as many times as its length to create shifted patterns of the string as depicted…
gkoul
- 1,067
- 1
- 10
- 19
1
vote
2 answers
Checking if a string is valid with special chars
I'm using PATINDEX to validate if a column has invalid special chars. But I'm facing some problems with some chars.
SELECT PATINDEX(N'%[^a-zA-Z0-9 !"&''()*+,-./:;?=%~@[]_{}\|<>]%' collate SQL_Latin1_General_CP850_BIN, 'abc╢123' collate…
jpmo22
- 77
- 8
1
vote
0 answers
Query performance of searching pattern
I have the following sample data for understanding the requirement.
Table:
create table ft_test
(
col1 int identity(1,1),
col2 varchar(max)
);
insert into ft_test values('John A Henry');
insert into ft_test values('Dsouza mak Dee');
insert…
MAK
- 6,824
- 25
- 74
- 131
1
vote
4 answers
Finding out just the numeric part from string and storing it as decimal datatype
I tried using patindex to find the starting of the number value in a string. My goal is to just extract the numeric part from the string excluding the %.
With the below query, here are my results :
Select Column_Desc,…
MRBasun
- 83
- 6
1
vote
1 answer
T-SQL Wildcard search - namespace values
I need to satisfy a condition in a string that has "ns[0-9]:" where [0-9] can be any number even greater than 10.
Example:
DECLARE @test TABLE ( value VARCHAR(20))
INSERT INTO @test VALUES
( 'ns1:'),
( 'NOT OK'),
( 'ns7:'),
( 'ns8:'),
( 'ns9:'),
(…
Michael Pearson
- 31
- 3
1
vote
0 answers
Find end character index of Patindex match
I have a PATINDEX function as appears below which returns the starting index of the string as I'd like it to but given the wild cards, I will have an unknown end of string which will be required for string manipulation. I wanted to know if it was…
Krishn
- 813
- 3
- 14
- 28
1
vote
2 answers
mssql patindex for ] symbol
How to specify ] symbol inside character class (MS SQL SERVER PATINDEX function)?
'%["[]%' - for starting bracket - it works
'%["]]%' - for ending - it does not
wUser
- 23
- 4
1
vote
2 answers
TSQL Using SUBSTRING PATINDEX and STUFF to Amend Data
TSQL MSSQL 2008r2
I need help to amend data.
I've got so far and now I need help.
Sample Data
[EDIT] Additonal examples added
DECLARE @Table TABLE (NodePropertyValue NVARCHAR(50))
INSERT INTO @Table (NodePropertyValue)
VALUES
(N'AA11✏AAA…
Mazhar
- 3,797
- 1
- 12
- 29
1
vote
1 answer
SQL Server 2005 Query SUBSTRING & PATINDEX
i have in my database three word phrases:
for example: "JKH Drainage Units"
Does anyone knows how to get the first letter of the third word?
i need to extract the "U" of the "Units" word.
N.B. i tried to use SUBSTRING(Phrase, PATINDEX('% % %',…
Joy
- 45
- 1
- 4
1
vote
1 answer
Why does replace fail using PATINDEX for certain special characters?
We are trying to strip certain special characters from a string using the (simplified) command below, which is the most common solution we've seen after searching.
But, the results are inconsistent when using certain special characters. Can anyone…
LBerg
- 33
- 2
- 8
1
vote
2 answers
Remove Leading Zeros Except last Zero
Database - SQL Server 2012
I am currently using the following code:
substring(
SUBSTRING(col001, 59, 8),
patindex(
'%[^0]%',
SUBSTRING(col001, 59, 8)
),
10
) as TOTAL_DETAIL_RECORD_COUNT
A lot of substrings, I…
Gregory Brauninger
- 75
- 7
1
vote
2 answers
Check a word starting with specific string [SQL Server]
I try to search on a string like Dhaka is the capital of Bangladesh which contain six words. If my search text is cap (which is the starting text of capital), it will give me the starting index of the search text in the string (14 here). And if the…
Mahedi Sabuj
- 2,894
- 3
- 14
- 27