CODEWITHSUNDEEP
sqlsql-servert-sql
user7472983
user7472983

Reputation:

SQL Server delete after first occurence

I'm searching SCCM database for published applications

I get this output

163net_YoudaoDictionary_5.0_ZH_x32_CN_W10

But I want it to be 

163net YoudaoDictionary 5.0

So I need method to delete everything after _XX (were XX are some strings)

I used PATINDEX('%_[A-Z]%',la.DisplayName) to detect occurrence of _XX but how to instruct LEFT function to delete all after PATINDEX results are >1

Upvotes: 1

Views: 152

Answers (6)

user7472983
user7472983

Reputation:

Okay, i found a solution:there is view in SCCM database fn_LocalizedAppProperties(1033),column title, need to join it with fn_ListLatestApplicationCIs(1033) by CI_ID

Thanks everyone for suggestions !!

Upvotes: 0

gofr1
gofr1

Reputation: 15987

Sample table:

DECLARE @temp TABLE (
    string nvarchar(max)
)

INSERT INTO @temp VALUES
(N'163net_YoudaoDictionary_5.0_ZH_x32_CN_W10'),
(N'157org_WhateverDictionary_7.0_smth_KJ_x64_WN_W7'),
(N''),
(NULL)

Script:

SELECT RTRIM(REPLACE(LEFT(string,PATINDEX('%[_][A-Z][A-Z][_]%',string COLLATE Latin1_General_100_BIN2)),'_',' '))
FROM @temp

Output:

(No column name)
163net YoudaoDictionary 5.0
157org WhateverDictionary 7.0 smth

NULL

Explanation:

I guess you know what stands for RTRIM, REPLACE, LEFT, PATINDEX. And COLLATE Latin1_General_100_BIN2 binary collation means the search is a straight forward byte comparison, which is apparently quicker. More here Binary collations part.

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

If your string is always the same format and length, then you can use SUBSTRING() and REPLACE() functions:

DECLARE @Type NVARCHAR(MAX) = N'163net_YoudaoDictionary_5.0_ZH_x32_CN_W10';

SELECT SUBSTRING(REPLACE (@Type, '_', ' '), 0, 28) AS Result;

Use REPLACE() to replace _ with a space ' '.

Use SUBSTRING() to cut needed string from your string.

If you want to use PATINDEX():

SELECT REPLACE(SUBSTRING(@Type, 0, PATINDEX('%[_][A-Z][A-Z][_]%', @Type) ), '_', ' ') AS Result;

Update:

You did not provide a sample data, but here is an example:

CREATE TABLE MyData (
    STR NVARCHAR(MAX)
    );
INSERT INTO MYData VALUES
(N'163net_YoudaoDictionary_5.0_ZH_x32_CN_W10'),
(N'164net_YoudaoDictionary_4.0_HZ_x86_CN_W10_UD78_KIU7'),
(N'165net_YoudaoDictionary_4.1_ZH_x32'),
(N'166net_YoudaoDictionary_3.0_LF_x32_KO_X16'),
(N'167net_YoudaoDictionary_1.0_EX_x86_RW_B28');

SELECT REPLACE(SUBSTRING(STR, 0, PATINDEX('%[_][A-Z][A-Z][_]%', STR) ), '_', ' ') AS Result
FROM MyData;

Result:

+-----------------------------+
|           Result            |
+-----------------------------+
| 163net YoudaoDictionary 5.0 |
| 164net YoudaoDictionary 4.0 |
| 165net YoudaoDictionary 4.1 |
| 166net YoudaoDictionary 3.0 |
| 167net YoudaoDictionary 1.0 |
+-----------------------------+

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could use Substring() with Patindex() function to find the pattern (i.e. _XX) & replace with ' ' & then further replace '_' with ' ' 

DECLARE @DATA NVARCHAR(MAX)

SET @DATA = '163net_YoudaoDictionary_5.0_ZH_x32_CN_W10'

SELECT REPLACE(REPLACE(@DATA, SUBSTRING(@DATA, PATINDEX('%_ZH%', @DATA), LEN(@DATA)), ''), '_', ' ');

Result :

163net YoudaoDictionary 5.0

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37377

Try this:

declare @str varchar(100)
set @str = '163net_YoudaoDictionary_5.0_ZH_x32_CN_W10'
select REPLACE(LEFT(@str, PATINDEX('%[_][A-Z][^a-z]%',@str COLLATE SQL_Latin1_General_CP1_CS_AS) - 4), '_', ' ')

Essentially, you need such regular expression '%[_][A-Z][^a-z]%' to find what you need and set collation of string searched to case-sensitive, such as SQL_Latin1_General_CP1_CS_AS.

Upvotes: 2

gotqn
gotqn

Reputation: 43636

Use this:

DECLARE @DataSoruce TABLE
(
    [value] NVARCHAR(4000)
);

INSERT INTO @DataSoruce ([value])
VALUES ('163net_YoudaoDictionary_5.0_ZH_x32_CN_W10')

SELECT REPLACE(SUBSTRING([value], 0, PATINDEX('%[_][A-Z][A-Z]%',[value] COLLATE Latin1_General_100_BIN2)), '_', ' ')
FROM @DataSoruce;

Upvotes: 3

Related Questions