SQLTeam.com | Weblogs | Forums

Move things in parentheses to a new field

So I've been using a query to move things in parentheses to a new field. My coding is like this:

UPDATE DBupdate
SET     Title = i.Expr1, ShortDesc1 = i.Expr2
FROM  (SELECT SUBSTRING(Title, 0, CHARINDEX('(', Title)) + SUBSTRING(Title, CHARINDEX(')', Title) + 2, LEN(Title)) AS Expr1, SUBSTRING(Title, CHARINDEX('(', Title) + 1, CHARINDEX(')', Title) - CHARINDEX('(', 
            Title) - 1) AS Expr2, ProductNumber
FROM  DBupdate where title like '%(%)%') AS i CROSS JOIN
            DBupdate
WHERE (DBupdate.Title LIKE '%(%)%') AND (DBupdate.ProductNumber = i.ProductNumber) and  (NOT (DBupdate.Title LIKE '%([A-Z])%')) and  (NOT (DBupdate.Title LIKE '%([0-9])%')) AND (NOT (DBupdate.Title LIKE '%()%')) AND (NOT (DBupdate.Title LIKE '%( )%')) AND (NOT (DBupdate.Title LIKE '%('')%')) AND (NOT (DBupdate.Title LIKE '(%'))

The error I'm getting is "Invalid length parameter passed to the LEFT or SUBSTRING function." What could be doing that and how can I fix it?

Thanks

You need to provide some sample data in directly usable form, that is, a CREATE TABLE and INSERT statement(s).

Sorry for the multiple things in the list. One of these is causing the error:

BAD TO THE BONE (COLV-YLW)
COZY LITTLE CHRISTMAS (COLV-GRN)
HARD DAY'S NIGHT UHD/BLU-RAY (4K-WBR-2PK)
CELEBRATION BLU-RAY (2PC-2PK)
PIANO THE UHD/BLU-RAY (4K-WBR)
TALK ABOUT IT (BLK-COLV-YLW)
ALL IS WELL (WITH MY SOUL-CDRP)
HEROES DE HOY (35TH ANNIVERSARY EDITION-COLV)
LOST TAPES (NUMBERED HEAVYWEIGHT VINYL-OGV)
ALL GROWN UP (COLV-GATE-LTD-PURP)
OH MY GOTH (BLK-COLV-LTD-PURP)
DREAMS OF A MAD KING (BLK-COLV-DLX-GOL)
CHICK HABIT (COLV-RED-WHT)
DREAMS FOR THE DYING (BLK-COLV-GATE-PURP)
AMERICAN VAMPIRE (BLUE-BLUE-COLV-GATE)
SOUL REVOLUTION PART II (COLV-LTD-RED)
ANGELUS APATRIDA (W/CD-COLV-YLW-GER)
GONE WITH THE WIND IS MY LOVE (COLV-CREM-GER)
CRYPT OF ICE (COLV-RED-YLW-GER)
LUCIFER IV (LTD-GER)
TIME WAITS FOR NO SLAVE (COLV-ORG-GER)
UTILITARIAN (COLV-GRY-GER)
IN THE FOREST OF THE DREAMING DEAD (COLV-RED)
ATAVISTIC BLACK DISORDER / KOMMANDO (COLV-DLX)
NIGHTMARE OF BEING (BLUE-COLV-POST-GER)
ATAVISTIC BLACK DISORDER / KOMMANDO (COLV-GRY)
ARGENT MOON (BLUE-COLV-GER)
BORKNAGAR (25TH ANNIVERSARY-LTD-DIG-GER)
MASSTADEN UNDER VATTEN (LTD-GER)
ORIGIN (LTD-GER)
PORNOGRAPHIC SEIZURES (POST-REIS-GER)
DEATH MADNESS HORROR DECAY (LTD-DIG-GER)
PAGAN RHYTHMS (LTD-GER)
BLIND (BLK-COLV-GATE-PNK-GER)
VICTORY IN BLOOD (LTD-GER)
30 YEARS OF ORIENTAL METAL (LTD-GER)
E SUCCESSO A MORGAN (COLV-GRN-OFGV-ITA)
SUDDANCE (COLV-LTD-OGV-ORG-ITA)
DOOM & MOOD (COLV-ITA)
DOOM (AUTO-GER)
DOOM (AUTO-GER)
EXPLORING ALCATRAZ-DOCUMENTARY SERIES (DVD-2PC)
INVISIBLE BAND (ANIV-RMST)
RESIDENT EVIL-MULTI-FEATURE (4K-BOX-WBR)
SYLVIA KRISTEL 1970S COLLECTION (4PC-4PK)
HELLO LAS VEGAS (BOBO-ILL)
AXIS (CLEAR PURPLE VINYL-COLV-CVNL-PURP)
IT'S A HOLIDAY SOUL PARTY (COLV-RED-WHT)
WORSHIP THE ETERNAL DARKNESS (GATE-POST-WB)
NO TIME TO DIE / O.S.T. (COLV-GOL-LTD)
NO TIME TO DIE / O.S.T. (007 SYMBOL VERSION-LTD)
REPRISE (LTD-WBR)
CLOSE YOUR EYES & BREATHE (ORANGE VINYL-COLV)
LIVE IN SYDNEY '21 (GATE-LTD)
DEAD BEST (COLV-YLW-DLCD)
PEER GYNT (HIGHLIGHTS-HLTS)
PUNK ROCK ANTHOLOGY 1978-2017 (COLV-OFGV-RED)
LOVELY (BLUE-COLV-OFGV-UK)
PURE (COLV-OFGV-WHT-UK)
GALORE (COLV-OFGV-RED-UK)
PROGRESS TRAP (OGV-DLCD)
LAST OF GARDEN (OGV-DLCD)
SHLEEP (W/CD-LTD-REIS)
YOU WILL NEVER KNOW WHY (GATE-LTD)
PAEAN TO WILSON (DLX-3PK)
WILDEST DREAMS (LTD-UK)
CANTATAS CHOIRS & SYMPHONIC MUSIC (W/BOOK-LTD)
NIGHT MUSIC (FIRST RECORDINGS-W/CD-3PK)
DNA RE)SEQUENCED (COLV-CVNL-LTD)
HAVANA MOON (4PC-W/CD-W/DVD-DLX DOL DTS)
OUTSIDER (BLUE-COLV-LTD)
SURROUNDED BY TIME: HOURGLASS EDITION (DLX-UK)
CAPTAIN (DLX-EXP-AUS)
SCHEHERAZADE AND OTHER STORIES (W/DVD-EXP)
WICCA YEAR OF MAGIC (HCVR-ILL)
DIE 20. 3ER BOX (FOLGEN 59-61-GER)
KRIMI-BOX 27 (FOLGEN 199-202-GER)
UZI (COLV-GRY-LTD-POST)
EDEN'S ISLAND (EXTENDED EDITION-BLK)
EDEN'S ISLAND (EXTENDED EDITION-COLV-GRN)
EDEN'S ISLAND (WOOD SLIPCASE-CLEAR FOREST/LEAVES
FAROUK ENJINEER (GATE-LTD)
FEEL LIKE MAKING LIVE (ORANGE VINYL-COLV-LTD)
SUGGESTED FUNCTION EP 5 (STIC-JPN)
INVISIBLE GIRLS (W/DVD-OGV)
NIKKI NACK (COLV-LTD)
INDIANOLA MISSISSIPPI SEEDS (AUDP-COLV-GATE)
ANTHOLOGY-GREATEST HITS (AUDP-GATE-LTD-OGV)
WIZARD A TRUE STAR (AUDP-BLUE-COLV-GATE)
WIZARD A TRUE STAR (AUDP-CVNL-GATE-LTD)
OCEAN TALES (BLUE-COLV)
AKIRA-MOVIE (4K-2PK-SUB)
A3: SEASON AUTUMN & WINTER (2PC-2PK DIGC SUB)
ONE PIECE: SEASON 11 VOYAGE 6 (4PC-BOX SUB)
DEVIL IS A PART TIMER: SEASON 1 (2PC-2PK DIGC)
BASILISK: COMPLETE SERIES (3PC-3PK SNAP SUB)
PSYCHIC SCHOOL WARS-MOVIE (2PC-W/DVD-2PK)
INARI KON KON: COMPLETE SERIES (2PC-2PK SUB)
RADIANT: SEASON ONE-PART TWO (4PC-W/DVD)
DUCK STAB! ALIVE! (W/DVD-10IN-WB)
TIPPING POINT (COLV-GOL)
1971-1979 THE BAND YEARS (WB-RMST-DIG)
SUMMER OF 84 (WBR-2PK)
SECRET WORLDS (ADCB-PPBK)
ETERNAL HOME (BLK-GATE)
METALEPSIS (COLV-GRN)
OMOIDE NO UCHIGAWADE / WHO ARE YOU (COLV-RED)
MIDNIGHT PRETENDERS / I'M IN LOVE (COLV-PNK)
CONFIDENT (CLN-DLX)
RISE VULCAN SPECTRE (2021 VERSION-UK)
GLIDE (LTD-REIS)
GLIDE (LTD-REIS)
BUT ONLY AFTER YOU HAVE SUFFERED (BLK-COLV)
LET'S GO ON & ON & ON WITH HAM1 (DIG-CAN)
BABY WE CAN WORK IT OUT (DIG-CAN)
GLASS (STIC-PHOB-PHOT-ASIA)
ONLY LOVERS LEFT (RANDOM COVER-POST-STIC-WB)
RE:ALIZE (RANDOM COVER-ASIA)
STRANGE EFFECT (W/CD-UK)
ECHOES OF JOY (BLUE-COLV-UK)
KHAMAI LION (COLV-RED-YLW-CAN)
MYDATA (COLV-ORG-CAN)
GOIN' YOUR WAY (DELUXE EDITION-AUS)
PLAYS LIVE (2002 RE-MASTERED-HK)
TIME CAPSULE: ASTRO 2021 PHOTOBOOK (W/DVD-POST)
TEMPTATION (STIC-PHOB-PHOT-ASIA)
OFFERINGS (RMST-UK)
CANDLES IN THE RAIN: 50TH ANNIVERSARY (RMST-AUS)
SOMETHING TO LEAVE THE HOUSE FOR (COLV-PNK-UK)
SOMETHING TO LEAVE THE HOUSE FOR (BLUE-COLV)
DOUBLE ALLERGIC (20TH ANNIVERSARY PRESSING-AUS)
SCREEN VIOLENCE (BLUE-COLV-LTD)
PRISON BREAK: THE COMPLETE SERIES (25PC-UK)
FAUSTIAN DREAM (COLV-WHT-UK)
FEEL SORRY FOR THE FANATIC (COLV-YLW-UK)
SHAMBLEMATHS II (COLV-RED-UK)
TOTALLY NAKED RAYGUN (BOX-UK)
K(NO)W THEM K(NO)W US (UK)
NEON BODY (COLV-SPA)
GIU LA TESTA / O.S.T. (CVNL-LTD-ITA)
GODSPEED (COLV-GRN-UK)
LOVE SIGNS (COLV-YLW-AUS)
IMPORTANCE OF BEING ESPETACULAR (LTD-UK)
BEETHOVEN: COMPLETE PIANO CONCERTOS (WBR-CAN)
ASTRAL WEEKS (EXPANDED & REMASTERED-JPN)
OU EST PASSEE LA NOCE (1975-CAN)
BR:EVOLUTION (3RD EP-EP-ASIA)
GREATEST HITS (BRIGHT WHITE EDITION-GER)
AHA SHAKE HEARTBREAK (MPDL-UK)
BLIND PACKAGE (POST-PHOB-PHOT-ASIA)
PENTHOUSE (SBS DRAMA) / O.S.T. (ASIA)
AMY (PCRD-PHOB-PHOT-ASIA)
WWE LEGENDS (4PCAUS-NTR0)
WWII PIVOTAL BATTLES (6PCAUS-NTR0)
CAMPO DI MARTE (CVNL-LTD-ITA)
TEMPLE OF THE NEW SUN (COLV-GRN-UK)
IT'S A COOL COOL CHRISTMAS / VARIOUS (COLV-LTD)
REQUISITE JOY (BLUE-COLV-UK)
P.A.&JSAMMICH (BLUE-COLV-UK)
LOCKDOWN SESSIONS (BONUS DVD-UK)
MAKE YOUR MARK (COLV-RED-UK)
LE CHAT / LE TRAIN / O.S.T. (LTD-ITA)
MONUMENTS (CD+DVD PAL REGION 2-W/DVD-PAL2)
25 (BONUS DVD-CAN)
BADGERS OF WYMESWOLD (COLV-CREM-RED-UK)
SYSTEM (COLV-UK)
DOPAMINE (BLUE-COLV-UK)
PRIMAVERA EP / SONHO EP (COLV-YLW-UK)
SWEET MORNING (BLUE-COLV-UK)
TURN UP: TYPE D (BONUS TRACK-JPN)
CEYLON (RMST-CAN)
SNOW DAY (10IN-CAN)
BEAUTIFUL YOUNG GENERATION (OGV-UK)
DREAMHOUSE (BLUE-COLV-LTD-OGV-UK)
DOWN ON THE BIKINI LINE (COLV-PNK-CAN)
HER (EP-WB-ASIA)
HIDE & SEEK (HIDE VERSION-POST-STIC-PHOB)
HIDE & SEEK (SEEK VERSION-POST-STIC-PHOB)
STEREOTYPE (POST-PCRD-PHOB-PHOT-ASIA)
MOMENTS IN BETWEEN (WB-ASIA)
MI CASA SU CASA (STIC-PCRD-PHOB-PHOT-ASIA)
CHOCOLATE BOX (RANDOM COVER-ASIA)
1+1=1 (STIC-PCRD-PHOB-PHOT-ASIA)
I SAY MAMAMOO: THE BEST (PCRD-PHOB-PHOT)
TURBULENCE (PCRD-PHOB-PHOT-ASIA)
ICONS (STIC-WB-PHOT-ASIA)
POLICE UNIVERSITY (KBS DRAMA) / O.S.T. (WB-ASIA)
BLIND (PCRD-PHOB-ASIA)
NOT FRIENDS (RANDOM COVER-PHOB-PHOT-SPEC)
STAIRS (WB-PHOT-ASIA)
MAVERICK (POST-PHOB-PHOT-ASIA)
FIRE SATURDAY (RANDOM COVER-STIC-PHOB-PHOT)
FIRE SATURDAY (HAT-STIC-PHOB-PHOT-ASIA)
DREAMS ALIVE (PCRD-PHOB-PHOT-ASIA)
LOVE & LIE (REPACK B-ASIA)
MINI VOL 2 (#-NORMAL A-REIS-ASIA)
THIRD EAR BAND (AKA ELEMENTS-CAN)
NOCTURNE REMIXES (EP-UK)
BARDAINNE JAUMET (EP-UK)
YOU & ME WE ARE THE SAME (DLX-UK)
NEVER TRUST A WHITE MAN (COLV-WHT-ITA)
HYPOTHERMIA (EP-UK)
DECADE (BLK-COLV-RED-UK)
FLY OR DIE LIVE (COLV-UK)
CITY OF MIRRORS (COLV-UK)
EAGLEBREEZE (COLV-GOL-CAN)
DARK DREAM (STIC-WB-PCRD-PHOT-ASIA)
HELLO (REIS-ASIA)
ONE (MAKE VERSION-REIS-ASIA)
ONE (THIS VERSION-REIS-ASIA)
SEAT-BELT (STIC-PHOB-PHOT-ASIA)
HOT & COLD (STIC-PCRD-PHOB-PHOT-ASIA)
WITH THAT DAY THAT WASN'T MUCH (PCRD-PHOT)
LOOK FORWARD TO FAILURE (10IN-CAN)
EV'RY TIME I FEEL THE SPIRIT (1930-47-GER)
HOARSE (COLV-SPA)
LES PLUS BELLES COMPTINES D'OKOO (DLX-GER)
GRANDMAS HOUSE (COLV-UK)

The field that it's going to is a nvarchar(50).

please provide your data not as a wall of data but useable

create table #taunt(col1 int, col2 varchar(250))

insert into #taunt
select 1, 'your wall of data here 1 row per'

drop table #taunt

EDEN'S ISLAND (WOOD SLIPCASE-CLEAR FOREST/LEAVES has not closing ) so charindex is returning 0

I figured it out. It was the above that was the issue. With the extra end parentheses that was messing it up.

also

EDEN'S ISLAND (WOOD SLIPCASE-CLEAR FOREST/LEAVES

maybe you should look at the following from @JeffModen

select * 
  from DBupdate du
  cross apply DelimitedSplitN4K(du.Title, '(')