Post reply

How to Remove a character in the middle of a stirng

  • July 10, 2009 at 10:39 am

    #95559

    Does any one know how of any function to remove the any characer in the middle of a string?

    Example

    Create TABLE [dbo].[#tblZ](

    tblKey int identity(1,1) NOT NULL,

    [Col1] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col2] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col2] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    INSERT INTO #tblZ(Col1, Col2, Col2)

    SELECT 'PQ19188PFHAA','AMR','20685' UNION ALL

    SELECT 'PQ19188PFHAB','AVE','20686'

    I need to have the following output in these two rows

    PQ19188PFAA

    PQ19188PFAB

    (remove the H in each row)

    Thanks for the haelp.

  • July 10, 2009 at 10:43 am

    #1023205

    Use replace function.

    select replace ('PQ19188PFAA','H','')


    Pradeep Singh

  • July 10, 2009 at 10:44 am

    #1023207

    Look up the STUFF function in BOL (Books Online, the SQL Server Help System). Based on the description of your problem, this is just what you need.

    select stuff('PQ19188PFHAA',10,1,'')

  • July 10, 2009 at 10:45 am

    #1023208

    first, let me thank you for the CREATE TABLE and INSERT code; it helps enourmously. way to go!

    here's one way to do it; here i'm assuming the column is exactly 12 characters:

    Create TABLE [dbo].[#tblZ](

    tblKey int identity(1,1) NOT NULL,

    [Col1] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col2] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col3] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

    INSERT INTO #tblZ(Col1, Col2, Col3)

    SELECT 'PQ19188PFHAA','AMR','20685' UNION ALL

    SELECT 'PQ19188PFHAB','AVE','20686'

    select len(col1),left(col1,9) + right(col1,2) As Col1Fixed,* from #tblZ

    where len(col1) = 12

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2009 at 10:46 am

    #1023209

    ps (7/10/2009)

    Use replace function.

    select replace ('PQ19188PFAA','H','')

    Possibly, but the original request actually asked how to remove the 10th character. What if the character to be removed from the 10th position wasn't an 'H'.

  • July 10, 2009 at 10:47 am

    #1023211

    The problem is that this character is not always H, it can be any character. How can I use the Len function to get a fixed character in the string and replace it?

  • July 10, 2009 at 10:51 am

    #1023214

    Lowell (7/10/2009)

    first, let me thank you for the CREATE TABLE and INSERT code; it helps enourmously. way to go!

    here's one way to do it; here i'm assuming the column is exactly 12 characters:

    Create TABLE [dbo].[#tblZ](

    tblKey int identity(1,1) NOT NULL,

    [Col1] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col2] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Col3] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

    INSERT INTO #tblZ(Col1, Col2, Col3)

    SELECT 'PQ19188PFHAA','AMR','20685' UNION ALL

    SELECT 'PQ19188PFHAB','AVE','20686'

    select len(col1),left(col1,9) + right(col1,2) As Col1Fixed,* from #tblZ

    where len(col1) = 12

    Definitely would work, but a lot of extra work when the STUFF function can do it easily.

    See my first post above.

  • July 10, 2009 at 11:05 am

    #1023222

    Thank you, the function below worked..

    select stuff('PQ19188PFHAA',10,1,'')

    Thanks again.

  • July 10, 2009 at 11:09 am

    #1023225

    i saw that after you posted....i'm embarrassed to offer it now , since STUFF is such a better solution

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2009 at 11:13 am

    #1023227

    josetur12 (7/10/2009)

    Thank you, the function below worked..

    select stuff('PQ19188PFHAA',10,1,'')

    Thanks again.

    Thanks for the feedback.

  • July 10, 2009 at 11:45 am

    #1023245

    It is easier to implement.

  • July 10, 2009 at 12:22 pm

    #1023269

    Lynn Pettis (7/10/2009)

    select stuff('PQ19188PFHAA',10,1,'')

    Thanks Lynn. I'd never used that. Learnt something new here 🙂


    Pradeep Singh

  • July 10, 2009 at 2:56 pm

    #1023359

    Wow, that is nice. I had no idea such a thing existed. I need to note this down. Thanks for the valuable information.:cool:

    --
    :hehe:

  • July 13, 2009 at 12:19 pm

    #1024086

    stuff ?

    pls dont tell me theres a function called "Something" or another function called "AllINeedToGetTheJobDone"

    Select AllINeedToGetTheJobDone('Im lazy') from PolimorficTableThatKnowsEverything

    XD

    i really laughed with the name of the function. learned something new here. txs. 😀

  • July 14, 2009 at 9:42 am

    #1024512

    The STUFF function should only be executed if the 10th character is an 'H'. Therefore, the STUFF function should be used in combination with a SUBSTRING function and a CASE expression:

    DECLARE @input_value VARCHAR(10)

    DECLARE @output_value VARCHAR(10)

    SET @input_value = 'PQ19188PFHAA'

    SET @output_value = (SELECT CASE SUBSTRING(@input_value, 10, 1)

    WHEN 'H' THEN STUFF(@input_value, 10, 1, '')

    ELSE @input_value

    END

    )

    -- **********************************************

    I hope this small modification helps.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply