Member Avatar for jovillanuev

Guys,

I have those records. how to i removed the first 10 records and the last 2 records(-U)in string.

P300-1110-C731-U
P300-1110-INCREDIBLE-U
P300-1710-DEVOUR-U
P300-1710-DROID2-U
P300-4410-THUNDRBLT4G-U
P333-4110-8530-U


Here is the correct Result:

C731
INCREDIBLE
DEVOUR
DROID2
THUNDRBLT4G
8530

Thanks in Advance.

Jonel

  • 3 Contributors
  • 2 Replies
  • 74 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

wanted to drop the fixed 10-character prefix and the trailing "-U". asked what was tried and pointed toward string functions. Two practical SQL Server options follow: a simple fixed-length formula when the prefix/suffix are constant, and a more robust token-based method that finds the second and last hyphen so it works even if the internal token length varies.

-- fixed prefix (10 chars) and trailing "-U"
SELECT SUBSTRING(RTRIM(col), 11, LEN(RTRIM(col)) - 12) AS Extracted
FROM dbo.YourTable;

-- robust: find second hyphen and last hyphen, then extract the middle token
SELECT t.col,
       SUBSTRING(s.s, v2.pos2 + 1, v3.posLast - v2.pos2 - 1) AS Extracted
FROM dbo.YourTable t
CROSS APPLY (SELECT RTRIM(t.col) AS s) s
CROSS APPLY (SELECT CHARINDEX('-', s.s) AS pos1) v1
CROSS APPLY (SELECT CHARINDEX('-', s.s, v1.pos1 + 1) AS pos2) v2
CROSS APPLY (SELECT LEN(s.s) - CHARINDEX('-', REVERSE(s.s)) + 1 AS posLast) v3
WHERE v2.pos2 > 0 AND v3.posLast > v2.pos2 + 1;

Notes: use RTRIM to avoid LEN issues with trailing spaces. Guard positions with a WHERE or CASE to avoid negative lengths when data does not match the expected pattern. If every value is exactly four hyphen-separated tokens, the compact PARSENAME(REPLACE(...),2) trick is another option, but PARSENAME only supports up to four parts. Test on representative rows before running an update.

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

Lets see what you have tried so far.

Member Avatar for adam_k

You need to read about len and substring functions or left, right and len functions.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.