CODEWITHSUNDEEP
sqlsql-server
Jacky Montevirgen
Jacky Montevirgen

Reputation: 317

How to use substring and PatIndex in SQL Server

How to substring data. For example, I have data like this:

+----------------------+
|John123123412412wqeqw |
|May1231243334234wawdd |
|Jo02930124010284jahdj |
|dy827837127912938hygb |
+--------------------- +

I want the output to be like this:

+----------------------+
|John                  |
|May                   |
|Jo                    |
|dy                    |
+--------------------- +

As of now I don't understand how to Apply Substring and charindex in my script.

Thanks in advance!

Upvotes: 0

Views: 11090

Answers (2)

Mahesh
Mahesh

Reputation: 8892

Best way to handle the strings and there manipulation is in application side and not in database side. But if you required this then you can use PATINDEX along with SUBSTRING to get what you want,

SELECT PATINDEX('%[^0-9]%',stringValueCol) 'Position of NonNumeric String Position',
SUBSTRING(stringValueCol,PATINDEX('%[^0-9]%',stringVlaueCol),PATINDEX('%[^A-Z]%',stringValueCol)-1) 'NonNumeric String'
FROM
  myTable

But I would still suggest do this manipulations in code and not on database side.

Upvotes: 1

Aruna
Aruna

Reputation: 12022

You can try LEFT and PATINDEX with a pattern to match the numbers like PATINDEX('%[0-9]%', 'John123123412412wqeqw').

Sample code

DECLARE @Text VARCHAR(500);
SET @Text = 'John123123412412wqeqw';

SELECT LTRIM(RTRIM(LEFT(@Text, PATINDEX('%[0-9]%', @Text) - 1)))

You can do this from the table as below

SELECT 
   LTRIM(RTRIM(LEFT(ColumnName, PATINDEX('%[0-9]%', ColumnName) - 1))) 
FROM 
   [Table1]

Upvotes: 3

Related Questions