Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Remove Letters/Symbols from String

Posted on 2011-10-27
Medium Priority
444 Views
I have an old table that displays an ID number as AA110000 or BBB110000 or CCC11-000.  What I need to do and extract the leading letters and - from the number...so all that displays is a 4 digit number.  So in the last case where the number is only three digits, I need it to lead with a 0.

Basically I am exporting this data from its old table to a new table as an INTEGER only field.  Now in that field I want to mask the number so it looks like 11-0000 when displayed.
0
Question by:Lee R Liddick Jr
• 11
• 10
• 7

LVL 50

Expert Comment

ID: 37039974
if your going to treat it as an integer then cast/convert it to an integer....

otherwise concatenate it with leading zeros....

like

select  right('0000'+right(yourcolumn,case when charindex('-',yourcolumn)> 0 then len(yourcolumn) - charindex('-',yourcolumn)
else 4 end),4)
from (select rtrim(youcolumn) as yourcolumn from yourtable) as x
0

LVL 70

Expert Comment

ID: 37040399
Is it always the right-most 4 digits, with or without a dash?  So that:
AA110000 --> 0000
BBB110000 --> 0000
CCC11-000 --> 0000

Then:

new_int_column = RIGHT('0000' + REPLACE(RIGHT(column, 4), '-', ''), 4)

In the new table, make the display value a computed column, like so:

CREATE TABLE ...
new_alphanum_column varchar(30),
new_int_column int,
display_column AS new_alphanum_column + '-' + RIGHT('0000' + CAST(new_int_column AS varchar(4)), 4)

Then in the SELECT, you can use "display_column" just like any other column.  It's no overhead, because SQL will only construct the column if it's actually used in the query.

That way the definition is in ONE place if it ever needs to change.
0

LVL 70

Expert Comment

ID: 37040431
If the "-" can leave fewer than 3 digits to its right, then you need to make a slight adjustment:

new_int_column = RIGHT('0000' + REPLACE(RIGHT(column, 4), '-', '000'), 4)
0

Author Comment

ID: 37040490
Okay well how do i get that incorporated in my current query:

SELECT ID, NAME
FROM MYTABLE
0

Author Comment

ID: 37040516
4 Numbers are always to the right Scott...
0

LVL 70

Expert Comment

ID: 37040649
Actually using '000' on the REPLACE doesn't hurt even no matter how many digits are to the right, so it's best to leave it as a precaution against fewer digits after the -.

ALTER TABLE MyTable
masked_column AS ID '-' + RIGHT('0000' + CAST(int_column AS varchar(10)), 4)
0

LVL 70

Expert Comment

ID: 37041193
(Assuming "ID" contains the varchar part of the value, and "int_column" contains the new int-only part):

ALTER TABLE MyTable
masked_column AS ID + '-' + RIGHT('0000' + CAST(int_column AS varchar(10)), 4)
0

Author Comment

ID: 37041394
Scott...your example worked as written but I am finding I have more variables in here than I thought.  I have:

AA9##
AA09###
9####
10###
10####
AA10###
AA10####
11####
AA11####
TCR11-###
NOTE:  The AA is either IN or FD

I'm guessing that is way too many variables and I'm going to have to manually fix this field.

0

LVL 50

Expert Comment

ID: 37041511
if that is the full range then its manageable

; with cte as (
select 'AA9' as starter
union all select 'AA09'
union all select '9'
union all select '10'
union all select '10'
union all select 'AA10'
union all select 'AA10'
union all select '11'
union all select 'AA11'
union all select 'TCR11-')
,cte1 as (select case x.n when 1 then replace(starter,'AA','IN')
when 2 then replace(starter,'AA','FD')
from (select distinct starter from cte) as c
cross join (select 1 as n union all select 2) as x
where x.n =1 or
(x.n=2 and starter like 'AA%')
)
SELECT stuff(ID,1,len(cte1.starter),'') as ID
, NAME
FROM MYTABLE
inner join cte1
on id like cte1.starter +'%'

0

LVL 50

Expert Comment

ID: 37041524
SELECT convert(integer,stuff(ID,1,len(cte1.starter),'')) as ID
0

LVL 70

Expert Comment

ID: 37042643
Below is one possibility.  If those prefixes are *always* to be excluded from the result, you can set the minDataLength to 1 for every entry.

It may need some tweaking, but you should be able to get to a programmatical solution.  NO reason you should have to do this by hand.
``````SELECT
CASE WHEN CHARINDEX('-', yt.dataColumn) > 0
THEN RIGHT('0000' + REPLACE(yt.dataColumn, '-', '0000'), 4)
WHEN prefixes.prefix IS NOT NULL
THEN SUBSTRING(yt.dataColumn, LEN(prefix) + 1, 100)
ELSE SUBSTRING(yt.dataColumn, PATINDEX('%[0-9]%', yt.dataColumn), 4)
END
FROM yourTable yt
LEFT OUTER JOIN (
SELECT '9', 4 AS prefix, minDataLength UNION ALL
SELECT '10', 4 UNION ALL
SELECT '11', 4 UNION ALL
SELECT 'FD09', 6  UNION ALL
SELECT 'FD10', 6 UNION ALL
SELECT 'FD11', 6 UNION ALL
SELECT 'FD9', 5 UNION ALL
SELECT 'IN09', 6  UNION ALL
SELECT 'IN10', 6 UNION ALL
SELECT 'IN11', 6 UNION ALL
SELECT 'IN9', 5
) AS prefixes ON
LEFT(yt.dataColumn, LEN(prefixes.prefix)) = prefixes.prefix AND
LEN(yt.dataColumn) >= prefixes.minDataLength
``````
0

Author Comment

ID: 37057483
In these examples, how can incooperate all of that into my current query?  I guess I should have added the query in my original question...
``````SELECT
MAN.intTempID,
MAN.intID,
intBU_ID =
CASE MAN.strSubmitBy
WHEN 'cb1166' then '3'
WHEN 'as3199' then '2'
ELSE '1'
END,
intCATEGORY_ID =
CASE
WHEN MAN.intID LIKE 'IN%' then '3'
WHEN MAN.intID LIKE 'FD%' then '2'
WHEN MAN.intID LIKE 'DD%' then '4'
WHEN MAN.intID LIKE 'TC%' then '5'
ELSE '1'
END,
intCHANGETYPE_ID =
CASE MAN.strCatItem
WHEN 'New' then '1'
WHEN 'Change' then '2'
WHEN 'Other' then '3'
WHEN 'Remove' then '4'
ELSE '5'
END,
STA.intStatID, SAP.intAprvlID,
STA.dtiUpdated,
STA.strUpdateBy,
DAT.dtiOpen,
MAN.strSubmitBy
FROM
dbo.main MAN INNER JOIN
dbo.data DAT ON MAN.intTempID = DAT.intTempID INNER JOIN
dbo.status STA ON MAN.intTempID = STA.intTempID INNER JOIN
dbo.approval SAP ON MAN.intTempID = SAP.intTempID

WHERE
STA.intID = (select max(b.IntID) from status B
where b.intTempID = STA.intTempID and
b.dtiUpdated >= STA.dtiUpdated
)
``````
0

LVL 50

Expert Comment

ID: 37057661
YES

you've lost me , please relate you query components to the initial question you asked...

which columns are being stripped, joined , and where is the display element...
0

Author Comment

ID: 37057691
So sorry...I should know you can't leave out the smallest of details and in this case, the entire query.  The 'intID' in the query is the field/column that holds all of the multiple variations that I need to strip.  What I am needing to do is to create a query that will provide me with the exact data (that I will export the query results to a spreadsheet) that I need to then import into the new table.
0

LVL 50

Expert Comment

ID: 37058001
try this

....

could you explain what this is trying to achieve

as its not quite gelling with me...

WHERE
STA.intID = (select max(b.IntID) from status B
where b.intTempID = STA.intTempID and
b.dtiUpdated >= STA.dtiUpdated
)

are you expecting multiple rows to be generated by the status table?
``````; with cte as (
select 'AA9' as starter
union all select 'AA09'
union all select '9'
union all select '10'
union all select 'AA10'
union all select '11'
union all select 'AA11'
union all select 'TCR11-')
,cte1 as (select case x.n when 1 then replace(starter,'AA','IN')
when 2 then replace(starter,'AA','FD')
from (select distinct starter from cte) as c
cross join (select 1 as n union all select 2) as x
where x.n =1 or
(x.n=2 and starter like 'AA%')
)
SELECT
MAN.intTempID,
convert(integer,stuff(MAN.intID,1,len(cte1.starter),'')) as intID,
intBU_ID =
CASE MAN.strSubmitBy
WHEN 'cb1166' then '3'
WHEN 'as3199' then '2'
ELSE '1'
END,
intCATEGORY_ID =
CASE
WHEN MAN.intID LIKE 'IN%' then '3'
WHEN MAN.intID LIKE 'FD%' then '2'
WHEN MAN.intID LIKE 'DD%' then '4'
WHEN MAN.intID LIKE 'TC%' then '5'
ELSE '1'
END,
intCHANGETYPE_ID =
CASE MAN.strCatItem
WHEN 'New' then '1'
WHEN 'Change' then '2'
WHEN 'Other' then '3'
WHEN 'Remove' then '4'
ELSE '5'
END,
STA.intStatID, SAP.intAprvlID,
STA.dtiUpdated,
STA.strUpdateBy,
DAT.dtiOpen,
MAN.strSubmitBy

FROM
dbo.main MAN
INNER JOIN
dbo.data DAT
ON MAN.intTempID = DAT.intTempID
INNER JOIN
dbo.status STA
ON MAN.intTempID = STA.intTempID
INNER JOIN
dbo.approval SAP
ON MAN.intTempID = SAP.intTempID
inner join cte1
on man.intid like cte1.starter +'%'

WHERE
STA.intID = (select max(b.IntID) from status B
where b.intTempID = STA.intTempID and
b.dtiUpdated >= STA.dtiUpdated
)
``````
0

Author Comment

ID: 37058045
The WHERE clause is created because the STATUS table I get the 'status id' from is a one-to-many relationship with the main record (intTempID)...so this gets the 'last' known status for that main record.
0

Author Comment

ID: 37058110
I got an incorrect syntax near ';' and other incorrect syntax errors.
0

LVL 50

Expert Comment

ID: 37058125
WHERE
STA.intID = (select max(b.IntID) from status B
where b.intTempID = STA.intTempID and
b.dtiUpdated >= STA.dtiUpdated
)

yes i expected it to be something like that, so why the >= on the dtiupdated column?

so i think i'd do it with just a subquery join
e.g.
this without the where clause

FROM
dbo.main MAN
INNER JOIN
dbo.data DAT
ON MAN.intTempID = DAT.intTempID
INNER JOIN
( select x.* from
(select Â¿s.*
,row_number() over (partition by s.inttempid order by intid desc) as rn
from dbo.status
) as x
where rn=1
)  STA

ON MAN.intTempID = STA.intTempID
INNER JOIN
dbo.approval SAP
ON MAN.intTempID = SAP.intTempID
inner join cte1
on man.intid like cte1.starter +'%'

0

LVL 50

Expert Comment

ID: 37058150
ok you need to post the errors or i can't debug....

i saw a missing END from the case in the cte...

confirm how you run this please.

?
``````; with cte as (
select 'AA9' as starter
union all select 'AA09'
union all select '9'
union all select '10'
union all select 'AA10'
union all select '11'
union all select 'AA11'
union all select 'TCR11-')
,cte1 as (select case x.n when 1 then replace(starter,'AA','IN')
when 2 then replace(starter,'AA','FD')
end
from (select distinct starter from cte) as c
cross join (select 1 as n union all select 2) as x
where x.n =1 or
(x.n=2 and starter like 'AA%')
)
SELECT
MAN.intTempID,
convert(integer,stuff(MAN.intID,1,len(cte1.starter),'')) as intID,
intBU_ID =
CASE MAN.strSubmitBy
WHEN 'cb1166' then '3'
WHEN 'as3199' then '2'
ELSE '1'
END,
intCATEGORY_ID =
CASE
WHEN MAN.intID LIKE 'IN%' then '3'
WHEN MAN.intID LIKE 'FD%' then '2'
WHEN MAN.intID LIKE 'DD%' then '4'
WHEN MAN.intID LIKE 'TC%' then '5'
ELSE '1'
END,
intCHANGETYPE_ID =
CASE MAN.strCatItem
WHEN 'New' then '1'
WHEN 'Change' then '2'
WHEN 'Other' then '3'
WHEN 'Remove' then '4'
ELSE '5'
END,
STA.intStatID, SAP.intAprvlID,
STA.dtiUpdated,
STA.strUpdateBy,
DAT.dtiOpen,
MAN.strSubmitBy

FROM
dbo.main MAN
INNER JOIN
dbo.data DAT
ON MAN.intTempID = DAT.intTempID
INNER JOIN
( select x.* from
(select Â¿s.*
,row_number() over (partition by s.inttempid order by intid desc) as rn
from dbo.status
) as x
where rn=1
)  STA
ON MAN.intTempID = STA.intTempID
INNER JOIN
dbo.approval SAP
ON MAN.intTempID = SAP.intTempID
inner join cte1
on man.intid like cte1.starter +'%'
``````
0

Author Comment

ID: 37058299
Msg170, Level 15, State 1, Line 1
Line 1:  Incorrect syntax near ';'.
Msg170, Level 15, State 1, Line 10
Line 10:  Incorrect syntax near ','.

Its just a query in SSMS that I execute to get the results.
0

LVL 50

Expert Comment

ID: 37058495
ok sorry  the missing end also required an AS Starter

``````; with cte as (
select 'AA9' as starter
union all select 'AA09'
union all select '9'
union all select '10'
union all select 'AA10'
union all select '11'
union all select 'AA11'
union all select 'TCR11-')
,cte1 as (select case x.n when 1 then replace(starter,'AA','IN')
when 2 then replace(starter,'AA','FD')
end as starter
from (select distinct starter from cte) as c
cross join (select 1 as n union all select 2) as x
where x.n =1 or
(x.n=2 and starter like 'AA%')
)
SELECT
MAN.intTempID,
convert(integer,stuff(MAN.intID,1,len(cte1.starter),'')) as intID,
intBU_ID =
CASE MAN.strSubmitBy
WHEN 'cb1166' then '3'
WHEN 'as3199' then '2'
ELSE '1'
END,
intCATEGORY_ID =
CASE
WHEN MAN.intID LIKE 'IN%' then '3'
WHEN MAN.intID LIKE 'FD%' then '2'
WHEN MAN.intID LIKE 'DD%' then '4'
WHEN MAN.intID LIKE 'TC%' then '5'
ELSE '1'
END,
intCHANGETYPE_ID =
CASE MAN.strCatItem
WHEN 'New' then '1'
WHEN 'Change' then '2'
WHEN 'Other' then '3'
WHEN 'Remove' then '4'
ELSE '5'
END,
STA.intStatID, SAP.intAprvlID,
STA.dtiUpdated,
STA.strUpdateBy,
DAT.dtiOpen,
MAN.strSubmitBy

FROM
dbo.main MAN
INNER JOIN
dbo.data DAT
ON MAN.intTempID = DAT.intTempID
INNER JOIN
( select x.* from
(select s.*
,row_number() over (partition by s.inttempid order by intid desc) as rn
from dbo.status
) as x
where rn=1
)  STA
ON MAN.intTempID = STA.intTempID
INNER JOIN
dbo.approval SAP
ON MAN.intTempID = SAP.intTempID
inner join cte1
on man.intid like cte1.starter +'%'
``````
0

Author Comment

ID: 37058539
Hmmmm, it still doesn't like the ; or the , ...
0

LVL 50

Expert Comment

ID: 37058676
ok i opened up my management studio (2008 r2 )

and have confirmed that the code syntax checks ok, and also
that the first part of the cte works correctly...

which version of sql server are you using?
do you have anything else in the query window?

if you highlight the whole query  does it syntax check ok?
does it execute?
0

Author Comment

ID: 37058714
Im using SSMS 2005 and it appears the dbase is in SQL 2000.
0

LVL 50

Accepted Solution

ID: 37059444
yes thats another of those important facts that we need to know upfront...

totally different "old-Skool" style of SQL required to effectively deal with SQL 2000

try this
``````SELECT
MAN.intTempID,
convert(integer,stuff(MAN.intID,1,len(cte1.starter),'')) as intID,
intBU_ID =
CASE MAN.strSubmitBy
WHEN 'cb1166' then '3'
WHEN 'as3199' then '2'
ELSE '1'
END,
intCATEGORY_ID =
CASE
WHEN MAN.intID LIKE 'IN%' then '3'
WHEN MAN.intID LIKE 'FD%' then '2'
WHEN MAN.intID LIKE 'DD%' then '4'
WHEN MAN.intID LIKE 'TC%' then '5'
ELSE '1'
END,
intCHANGETYPE_ID =
CASE MAN.strCatItem
WHEN 'New' then '1'
WHEN 'Change' then '2'
WHEN 'Other' then '3'
WHEN 'Remove' then '4'
ELSE '5'
END,
STA.intStatID, SAP.intAprvlID,
STA.dtiUpdated,
STA.strUpdateBy,
DAT.dtiOpen,
MAN.strSubmitBy

FROM
dbo.main MAN
INNER JOIN
dbo.data DAT
ON MAN.intTempID = DAT.intTempID
INNER JOIN dbo.status
as  STA
ON MAN.intTempID = STA.intTempID
INNER JOIN
dbo.approval SAP
ON MAN.intTempID = SAP.intTempID
inner join  (select case x.n when 1 then replace(starter,'AA','IN')
when 2 then replace(starter,'AA','FD')
end as starter
from (select distinct starter from (
select 'AA9' as starter
union all select 'AA09'
union all select '9'
union all select '10'
union all select 'AA10'
union all select '11'
union all select 'AA11'
union all select 'TCR11-') as cte) as c
, (select 1 as n union all select 2) as x
where x.n =1 or
(x.n=2 and starter like 'AA%')
) as cte1
on man.intid like cte1.starter +'%'

WHERE
STA.intID = (select max(b.IntID) from status B
where b.intTempID = STA.intTempID and
b.dtiUpdated >= STA.dtiUpdated
)
``````
0

LVL 70

Assisted Solution

Scott Pletcher earned 400 total points
ID: 37059651
Looks like MAN.intID is the column you're trying to adjust?

If not, just change MAN.intID in the added code to MAN.intTempID or whatever the right column name is.
``````SELECT
MAN.intTempID,
MAN.intID,
CASE WHEN prefixes.prefix IS NULL THEN MAN.intID --no match found in table, use original value
WHEN CHARINDEX('-', MAN.intID) > 0 THEN RIGHT('0000' + REPLACE(MAN.intID, '-', '0000'), 4)
WHEN prefixes.prefix IS NOT NULL THEN SUBSTRING(MAN.intID, LEN(prefix) + 1, 100)
ELSE SUBSTRING(MAN.intID, PATINDEX('%[0-9]%', MAN.intID), 4)
END AS NewMANintID,
intBU_ID =
CASE MAN.strSubmitBy
WHEN 'cb1166' then '3'
WHEN 'as3199' then '2'
ELSE '1'
END,
intCATEGORY_ID =
CASE
WHEN MAN.intID LIKE 'IN%' then '3'
WHEN MAN.intID LIKE 'FD%' then '2'
WHEN MAN.intID LIKE 'DD%' then '4'
WHEN MAN.intID LIKE 'TC%' then '5'
ELSE '1'
END,
intCHANGETYPE_ID =
CASE MAN.strCatItem
WHEN 'New' then '1'
WHEN 'Change' then '2'
WHEN 'Other' then '3'
WHEN 'Remove' then '4'
ELSE '5'
END,
STA.intStatID, SAP.intAprvlID,
STA.dtiUpdated,
STA.strUpdateBy,
DAT.dtiOpen,
MAN.strSubmitBy
FROM
dbo.main MAN INNER JOIN
dbo.data DAT ON MAN.intTempID = DAT.intTempID INNER JOIN
dbo.status STA ON MAN.intTempID = STA.intTempID INNER JOIN
dbo.approval SAP ON MAN.intTempID = SAP.intTempID LEFT OUTER JOIN
(
SELECT '9', 4 AS prefix, minDataLength UNION ALL
SELECT '10', 4 UNION ALL
SELECT '11', 4 UNION ALL
SELECT 'FD09', 6  UNION ALL
SELECT 'FD10', 6 UNION ALL
SELECT 'FD11', 6 UNION ALL
SELECT 'FD9', 5 UNION ALL
SELECT 'IN09', 6  UNION ALL
SELECT 'IN10', 6 UNION ALL
SELECT 'IN11', 6 UNION ALL
SELECT 'IN9', 5
) AS prefixes ON LEFT(MAN.intID, LEN(prefixes.prefix)) = prefixes.prefix AND
LEN(MAN.intID) >= prefixes.minDataLength
WHERE
STA.intID = (select max(b.IntID) from status B
where b.intTempID = STA.intTempID and
b.dtiUpdated >= STA.dtiUpdated
)
``````
0

Author Comment

ID: 37062610
That worked LowFat...that didn't Scott.

Msg 207, Level 16, State 3, Line 1
Invalid column name 'minDataLength'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'prefixes'.
0

LVL 70

Assisted Solution

Scott Pletcher earned 400 total points
ID: 37062730
Sorry, first line of sub-query got scrambled somehow:

SELECT '9', 4 AS prefix, minDataLength UNION ALL

should be:
...
SELECT '9' AS prefix, 4 AS minDataLength UNION ALL
...
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.â€‹
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Is your data getting by on basic protection measures? In todayâ€™s climate of debilitating malware and ransomwareâ€”like WannaCryâ€”that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.â€¦
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give iâ€¦
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll