REPLACE statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

REPLACE statement

Author

Topic

jphess
Starting Member

2 Posts

Posted - 2011-01-27 : 14:13:20

I am trying to do a replace in a Select statement. I want to replace the entire output (whatever it is) with just a word. I tried a wildcard and part of the output with a wild card, etc, but nothing is effective. I can implement just a match of the text such as '02W, '01T', but that is inefficient because the output varies. Is there any way to do this simply or even use a different command? Thanks in advance for any help!!

REPLACE (CLLI_CODE,’%’, ‘DESMIAIT01T’) AS CLLI_CODE
REPLACE (<column_name>, '<match>', '<replace for match>')AS CLLI_CODE

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-27 : 14:15:48

I'm not clear what you are trying to do. Do you have some sample data and expected output? Or a better example?

maybe this link will help?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jphess
Starting Member

2 Posts

Posted - 2011-01-28 : 07:39:14

I apologize for my non-clarity. I am a newbie to this...Anyway, my query is below.

INPUT
SELECT
REPLACE (CLLI_CODE,’%’, ‘DESMIAIT01T’) AS CLLI_CODE, (this is the line that I cannot get to work right. I want to replace any of the output of the CLLI_CODE with the DESMIAIT01T. I have tried multiple variations of this but I can't get any to work the way I need.)
TRUNK_GROUP_INFO.TRUNK_GROUP_DESCRIPTION AS SUB_RESOURCE,
CLLI_LOCATION.CLLI_CODE AS ALARM_QUERY_NODE,
NVL(CLLI_LOCATION.LOCATION_NAME, ‘LOC_NM_NULL’) AS PHY_LOC_NAME,
NVL(TCIC.SVC_SIGNALLING_POINTCD_A, ‘OPC_NULL’) AS ORIG_PC,
NVL(TCIC.TRUNK_GROUP.SVC_SIGNALLING_POINTCD_Z, ‘DPC_NULL’) AS TERM_PC,
NVL(COMPANY.OPERATING_COMPANY_NAME, ‘COMPANY_NULL’) AS OPERATING_COMPANY,
NVL((select count(TRUNK_NUMBER) from TRUNK_CIRCUIT where
TRUNK_CIRCUIT.TRUNK_GROUP_DESIGN_ID = TRUNK_GROUP.TRUNK_GROUP_DESIGN_ID), 0) AS TRK_COUNT,
NVL(TRUNK_GROUP.CIC, ‘N/A’) AS CAC
FROM TRUNK_CIRCUIT
RIGHT JOIN TCIC
RIGHT JOIN TRUNK_GROUP_INFO
LEFT JOIN TRUNK_GROUP
ON TRUNK_GROUP_INFO.TRUNK_GROUP_DESIGN_ID = TRUNK_GROUP.TRUNK_GROUP_DESIGN_ID
LEFT JOIN CLLI_LOCATION
LEFT JOIN COMPANY
ON CLLI_LOCATION.OPERATING_COMPANY_NUMBER = COMPANY.OPERATING_COMPANY_NUMBER
ON TRUNK_GROUP.LOCATION_ID = CLLI_LOCATION.LOCATION_ID
ON TCIC.LOCATION_ID_SPC_Z = TRUNK_GROUP.LOCATION_ID_SPC_Z
AND TCIC.LOCATION_ID_SPC_A = TRUNK_GROUP.LOCATION_ID_SPC_A
ON TRUNK_CIRCUIT.TRUNK_GROUP_DESIGN_ID = TRUNK_GROUP_INFO.TRUNK_GROUP_DESIGN_ID
WHERE TRUNK_GROUP_INFO.TRUNK_GROUP_DESCRIPTION LIKE ‘%IAIN’;

OUTPUT (The output in this case is just a snippet. The items in question are the "DESMIAITO79" in the first sample and "ALNAIABMO79" in the second, which is the first field pulled of CLLI_CODE The output can vary, but the format is always the same with 11 alphanumeric digits.

(1.)Example
DESMIAITO79 S1-R2-P1-TX
WHITEROCK VLX2020
101 /OC48 /DESMIAITO79/PLHLIAADO79

XXXX XXXXXXX XXXXXXXX
DESMIAITO79
PLHLIAADO79 OC48 175595
13 DESMIAITO79
Ring DESM9
DR9 WHITEROCK VLX2020 TO WILTEL

(2.)Example
ALNAIABMO79 S2-R1-P1-F3
WHITEROCK VLX2020
601 /ST03 /ALNAIABMO79/DVNPIA80O2Q
XXXX XXXXXXX XXXXXXXX

ST03 300090

4 ALNAIABMO79
Ring DESM9

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources