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
28
Medium Priority
?
444 Views
Last Modified: 2012-05-12
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
Comment
Question by:Lee R Liddick Jr
  • 11
  • 10
  • 7
28 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
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

by:Scott Pletcher
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

by:Scott Pletcher
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Lee R Liddick Jr
ID: 37040490
Okay well how do i get that incorporated in my current query:  

SELECT ID, NAME
FROM MYTABLE
0
 

Author Comment

by:Lee R Liddick Jr
ID: 37040516
4 Numbers are always to the right Scott...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
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
ADD
    masked_column AS ID '-' + RIGHT('0000' + CAST(int_column AS varchar(10)), 4)
0
 
LVL 70

Expert Comment

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

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

Author Comment

by:Lee R Liddick Jr
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

by:Lowfatspread
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

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

Expert Comment

by:Scott Pletcher
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

Open in new window

0
 

Author Comment

by:Lee R Liddick Jr
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
    )

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
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

by:Lee R Liddick Jr
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

by:Lowfatspread
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
    )

Open in new window

0
 

Author Comment

by:Lee R Liddick Jr
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

by:Lee R Liddick Jr
ID: 37058110
I got an incorrect syntax near ';' and other incorrect syntax errors.
0
 
LVL 50

Expert Comment

by:Lowfatspread
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

by:Lowfatspread
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 +'%'

Open in new window

0
 

Author Comment

by:Lee R Liddick Jr
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

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

please retry
; 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 +'%'

Open in new window

0
 

Author Comment

by:Lee R Liddick Jr
ID: 37058539
Hmmmm, it still doesn't like the ; or the , ...
0
 
LVL 50

Expert Comment

by:Lowfatspread
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

by:Lee R Liddick Jr
ID: 37058714
Im using SSMS 2005 and it appears the dbase is in SQL 2000.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1600 total points
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
    )

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
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
    )

Open in new window

0
 

Author Comment

by:Lee R Liddick Jr
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

by:Scott Pletcher
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question