Truncate part of the record

4 Nov 2005 2:22 AM

qjlee

I have a table called order, in which there is a field called ordertype. The
format of this record in this field is XXX---XXXXX, the number of Xs before
--- vary, and do not necessarily 3Xs and 5Xs. Is there any way I could
truncate whatever is after --- (including ---) and keep only whatever is
before ---.

Thanks,

4 Nov 2005 6:05 AM

Mike Hodgson

select substring(ordertype, 1, charindex('---', ordertype)-1) from mytable

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

qjlee wrote:

Show quote

>I have a table called order, in which there is a field called ordertype. The
>format of this record in this field is XXX---XXXXX, the number of Xs before
>--- vary, and do not necessarily 3Xs and 5Xs. Is there any way I could
>truncate whatever is after --- (including ---) and keep only whatever is
>before ---.
>
>Thanks,
>
>

9 Nov 2005 3:33 AM

qjlee

What if I want to truncate the left side and keep the right side after "---"

Thanks,

Show quote

"Mike Hodgson" wrote:

> select substring(ordertype, 1, charindex('---', ordertype)-1) from mytable
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> qjlee wrote:
>
> >I have a table called order, in which there is a field called ordertype. The
> >format of this record in this field is XXX---XXXXX, the number of Xs before
> >--- vary, and do not necessarily 3Xs and 5Xs. Is there any way I could
> >truncate whatever is after --- (including ---) and keep only whatever is
> >before ---.
> >
> >Thanks,
> >
> >
>

9 Nov 2005 4:51 AM

Mike Hodgson

select substring(
    ordertype,
    charindex('---',ordertype)+3,
    len(ordertype)-charindex('---',ordertype)-2
    )
from mytable

But you'll run into problems if '---' cannot be found in the ordertype
column because charindex() will return 0 and so the substring()
parameters will either be out of whack (which will give you the wrong
answer) or negative (which will return an error from the SQL Server).
So you should either guarantee the formatting of the column with a CHECK
constraint on the table or put a bit more intelligence/error handling
around your SELECT statement.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

qjlee wrote:

Show quote

>What if I want to truncate the left side and keep the right side after "---"
>
>Thanks,
>
>"Mike Hodgson" wrote:
>
>
>
>>select substring(ordertype, 1, charindex('---', ordertype)-1) from mytable
>>
>>--
>>*mike hodgson*
>>blog: http://sqlnerd.blogspot.com
>>
>>
>>
>>qjlee wrote:
>>
>>
>>
>>>I have a table called order, in which there is a field called ordertype. The
>>>format of this record in this field is XXX---XXXXX, the number of Xs before
>>>--- vary, and do not necessarily 3Xs and 5Xs. Is there any way I could
>>>truncate whatever is after --- (including ---) and keep only whatever is
>>>before ---.
>>>
>>>Thanks,
>>>
>>>
>>>
>>>

4 Nov 2005 6:07 AM

Roji. P. Thomas

Here you go.

CREATE TABLE #Temp(someID VARCHAR(15))

INSERT INTO #Temp VALUES('XXX--XXXXXX')
INSERT INTO #Temp VALUES('XX--XXXX')
INSERT INTO #Temp VALUES('X--XXXXXXXXXXX')
INSERT INTO #Temp VALUES('XXXXXXX--XXXXXX')

UPDATE #Temp
SET SomeId = LEFT(SomeId, CHARINDEX('--',SomeId)-1)

SELECT * FROM #Temp

DROP TABLE #Temp

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

Show quote

"qjlee" <qj***@discussions.microsoft.com> wrote in message
news:8C4A3015-A06A-42BA-B6D3-3AC392694477@microsoft.com...
>I have a table called order, in which there is a field called ordertype.
>The
> format of this record in this field is XXX---XXXXX, the number of Xs
> before
> --- vary, and do not necessarily 3Xs and 5Xs. Is there any way I could
> truncate whatever is after --- (including ---) and keep only whatever is
> before ---.
>
> Thanks,